[Music] welcome to platform analytics Academy on Wednesday July 24th we have another great session on tap with a um a really awesome speaker you all may know him we'll introduce him shortly so let's talk about uh what this is all about so obviously this is for you uh which try to bring fresh ideas better understanding and practical guidance uh as much as we possibly can to All Things platform analytics performance analytics reporting things of that nature this is being recorded uh it will be out in the community uh probably tomorrow and uh so feel free to sit back enjoy and watch uh so it'll also be out on YouTube as well so you can definitely rewatch this uh please ask questions if you can utilize the Q&A um we do have other panelists that are on to uh answer questions if we can answer those via type we'll do that if we need to answer them live and it's at a certain point in the content that we're going that's relevant we'll do that as well if they're unrelated to what the content that we're covering today we'll try to get to those um it maybe towards the end or we may direct you to just ask that question in the community Depending on time that we have uh Safe Harbor don't necessarily know if there's anything but uh it's always a good idea for us to make sure that we bring this up to you so please keep uh in mind Safe Harbor as you're actually looking at and listening to the content that we're providing you today so uh my name is Thomas Davis I am the host and we have Adam on everybody knows Adam is always excited about uh hearing from Adam he's going to present uh on formula indicators today and we have t as well T I love to uh love that we can have Tara come back and and join us so Tara and I'll be trying to answer questions as much as we possibly can while uh Adam is going over content so as I said before today we're going to talk about um specifically building formula indicators some use cases and some other things that Adam has added to it so super excited about this uh really G really great content that we'll add to our library that hopefully you'll be able to um use as you ask questions uh or have questions in the future as it relates to formula indicator so with that I'll pass it over to Adam if he is ready all right here we go I'm gonna overshare hopefully just make sure you share the right screen unlike me yeah still not working okay here we go we should see the slide up again yep all right so today we're talking about uh building formula indicators and use cases that is what Thomas asked me to talk about I ignore Thomas a lot so we're going to talk about formulas functions calculations and then more formulas so we're g to get the formula indicators uh but I wanted to talk about some other things that that are really useful in service now and why we use them and some other use cases that are similar to formula indicators but are not formula indicators so I want to make sure you have everything at your disposal to to resolve or to handle the issues and the use cases that you have so before we get to formula indicators we're going to talk about formulas functions calculations not necessarily in that order so we are going to talk about why are we use formulas at all whether it's formulas calculations or functions uh the different types of formulas that that we have we're going to go through those and and talk about formula fields and then we'll get into formula indicators and talk some more about use cases in there and we want to make sure that you you're aware of what you can do uh when we go through some of the training we talk about the basic things on how we do it but we want to make sure we talk about what can we do what's possible and then we'll wrap up with with Q&A all right so this is one of my favorite quotes I didn't ask the person if I could use it so this was a CTO that that I worked for I was in a meeting we were talking about analytics and this was the quote and this it's been a decade since I've heard this but it was so true don't make me think just don't make me think that's why we use formulas um I was explaining all you have to do is take that number and then divide it by that number and then you get what you're looking for looked me straight in the eye and said don't make me think just show me what I'm what what the real thing is what we're looking for and that's what we're going to try and resolve today so as we look at it and we think about formulas and and don't making me think it we want to talk about what's more actionable what do what do I see so do I want to look at the number of slas I I met this month month you know 372 or my SLA attainment how many did I meet outside of of that uh do I want to look at my total seat score I got all these sevens and eights and nines and I just sum them up and I have 532 or do I want to see the average of cessat right we always look at the average of cessat total hours of uptime or percentage of uptime no number of open cases a number sometimes that's useful but the percentage of growth is probably going to help me plan some more and then if I look at the number of cases or or do I want to look the number of cases or the average cases per agent in my is my team overloaded or these are all things that we we need to transform the raw counts into into numbers that are easier to understand easier to take action on um so why do we use them so it's easier comparison easier comparison so it's it is it simplifies comparing data sets this is we'll talk more about this at the end but I want to compare myself to other teams I want to compare myself to myself in previous periods and our organizations are always changing so if we're able to normalize the data with a formula we're able to really understand how we're performing uh standardization is really important as well so we do this all the time with percentages that's standardizing whatever the counts are to something that is consistent across teams across regions across time highlight those relative values uh Thomas might have have a 100 more incidents and I might have than I have but what we want to see is that relative growth is is my team growing faster even though I'm I'm less uh I have fewer incidents to take care of trend analysis over time way way easier to see when we're looking percentages in SLA attainment I don't want to have to look at the various uh various things that come through uptime the total uptime I don't have no idea how to compare that but if I'm normalizing that to an uptime percentage then I can see those changes over time and then data literacy there this was this what goes back to exactly to that quote before was well you have to know a lot of things to interpret a number is a certain number really good or bad but if I normalize it and I transform it using a formula or a function it's much easier to understand what the point of that is all right so I don't have a poll an actual poll because I did not tell Thomas about this beforehand um but I want to see in the chat we're going to talk about uh different types of formulas and and conversions and things like that so we're going to talk about calculated Fields formula Fields function fields and then formula indicators in the chat can you tell me which of these you're using or not using whatever comes through I just want to see that scroll through real quickly uh really quickly and so we can know what we're going to focus in on so we're using formula indicators formula indicators all of them all of them more all of them good good not using formulas yet great okay good so we have a lot of people who are familiar with all these so we're going to talk about some of the trade-offs and some use cases Maybe expand what we're deal what we're dealing with and for those of you who aren't familiar with them I want to make sure that we talk about what each one of these do because it's slightly different and so you want to make sure you're using the right the right solution in the right place so we are going to start with calculated Fields so calculated field the most common calculated field that we see uh in service now everybody uses it is the name field in CIS user so when I go to user we all have first names and we have last names and middle names at least out of the box those fields exist but there's a field called name and name concatenates first name middle name and last name unless there is no middle name and then it's just first name and last name with one space not with two spaces it's a little it's intelligent on what it can do we'll get get into this uh super useful um because everybody looks for full name I can search full name it's there and it calculates it for me um here we're seeing in the data dictionary a calculated field in this case it is calculating the estimated benefit so I have a field for the base estimated benefit and I have a field for the actual benefit and then it's going to calculate that Delta for me in in some app that we have in there I just went to the data dictionary and pulled one but it's just running JavaScript um a minus B gives me the value that we're going to look for again super useful these have been around uh all all of the things we're looking for now are supported I think in every release we have out there so you have access to all these calculated Fields have been around for a very very very very long time um useful it does what it does but let's talk about why we'd use it and why we wouldn't use it so some advantages and disadvantages of calculated Fields so the advantages it executes JavaScript you'll notice that the disadvantage is it executes JavaScript it has to execute that JavaScript it operates per row so each row at a time it's not meant to do a lot of aggregate stuff but it is looking at like we saw in the example A minus B gives me this new field and it can dotw walk it's a very easy to dotw walk in there um and it's always current what you C is always current is taking a minus B and showing you C that's really really useful if I'm doing a minus b equals c if I have dot locked Fields where the data might be changing underneath me and I can't see it the disadvantage is again it execute it executes JavaScript it's calculated every time you view it whether it changes or not so the classic example is the is name I wouldn't do it I names don't change very often They do change they can change but they don't change very often this is the third bullet here it's really important we'll see a little last it only it is only stored on an update it only stored on an update so CIS uh uh name is fine so if I change first name or last name it changes it changes the data that's there but if I were dot walking something else it it it's not going to store that it will display it correctly but it only gets stored when you're doing some other update whenever you're updating the record it's going to update those calculated Fields this calculated fields are part of the data dictionary they require admin to to create them um and it does physically store the result once you've updated it there is physically a value in there but that little Aster as we come back to it is because it's only stored on an update what you see is not necessarily what you query what you see is not what you query what you're querying that last value was updated on but what you're seeing is it's always calculated real time and it is always calculated to come through so if I'm reading a bunch of Records it's calculating all those records if I do more than just straight straight straight conversions that if that JavaScript is expensive that JavaScript took a second to execute and I want to look at 50 records I might be waiting 50 seconds so we got to be really careful on what we do here generally I discourage the use of calculated Fields they're there they're used in place of calculated Fields I do a little bit more work uh not a lot more but a little bit more work and I create a static field which is what we'd have to do for a calculated field but instead of making it calculated I leave it blank and I make it read only and then I populate it with a business rule or a flow so I can do a before business rule which will calculate it uh or a flow makes it much easier to do with a business rule I'm writing JavaScript with a flow I can actually use all the data pills all the Transformations that are there use an asynchronous on update business rule or flow and have it calculate that if I were implementing name today I would use a business rule or a flow I would do that because that way it's only being calculated when I update the first name middle name or last name field names don't change that often and other stuff does and I look at it a lot so why do I need to calculate that every time now a concatenation is not a very expensive calculation so it's not a big deal it's probably not worth changing but it's is something to think about does this change automatically do I need to see it all the time does it need to query it every time I view this record or is it something that I know when these things change that the function that I'm calculating for name is first name middle name and last name if one of those three do not change then I don't need to anything um as long as I'm letting my business rules run and my flows run you can do some system updates to bypass that to be aware of but an admin had to do that as well so under normal circumstances I normally lead to not using calculated Fields but using a static field a readon static field and then a flow or a business rule to calculate that um you do need to be able to detect the change so if I were doing a bunch of dot walking you can get into kind of too many places too many end points and notes to figure out when to calculate it and then I might still go to a calculated field although I'll probably look at some of other options but it's there this is key uh because when you it's calculated on on update and then stored and what you see is what you query you you know you're always setting that so if I query for something I'm that's what I'm going to see in query I don't have any of that weirdness in calculated Fields uh particular if you're doing something with time uh time gets me or for some aggregate that's going in the background what you see is not what your query all right we're going to move on to formula fields formula fields are are more of the advanced version of calculated fields to some extent much easier to use so I'm going to bring up the video and hope that this plays um so I am creating a calc a formula field here a formula field um I use this in I do this in table Builder and app engine studio and I will if we have time at the end I'm going to bring up uh an instance and we'll go walk through some of these uh live in the demo but here I'm able to just type what I want and it's going to prompt me for what the formulas are and I'm all set you can Nest these um it's uh type ahead it's prompting what what you're getting so whether it's concatenator Min or Max whole slew of of functions that you formula functions that you can use in your formula and it's parsing it as you go really really nice interface very very simple if you if you can do it in Excel you can do a formula field in table Builder let's get to some of the trade-offs with table Builder or with with formula fields and table Builder again super easy an advantage I don't know that there is anything easier to use than the formula builder in in table Builder it's always current the same Mo now what we're going to see is this is mostly like calculat Fields it's always current because it's being executed when you view the record and it operates per row um I believe you're able to dot walk here as well um but it's operating per row uh you do have to have access to app engine Studio or to table Builder to do it so you're in admin land you can have delegated development but uh you need admin access effectively to do this calculated every time same issue with calculated fields and formula fields that what is stored in the database and what's there it's not always the same this is a a a big concern that when you're creating a new calculated field if you're adding a calculated field or you're adding a formula to your table you need to do something that updates every record whether you're updating that field or you're just updating every record so that at least the initial value gets stored if I added a new name field and I didn't update every record that name Field's empty until some other update goes and goes does it in the background and again is physically stored takes up whatever space you're you're using so easy to use so you know there is definitely a use case for this very very easy to use formula Fields have set functions that you can use again similar to excel if I am doing uh if I just need to do something wild and crazy then I can use calculated Fields but if I'm doing something wild and crazy that concerns me and I'm worried about performance issues I'm worried about having to calculate this every every every every time I load that row generally I'm going to use formulas formula Fields over calculated Fields um and ideally I'm actually not going to use either one of these I'm going to use a function field so I calculated Fields formula Fields now we're going to get the function Fields function fields are different they operate differently and I have my example in the wrong spot so we're going to talk about the trade-offs and then we'll show you the the the build example so function Fields execute at the database level they're calculated on the Fly formula fields and calculated Fields execute on the Node level the data comes back and we operate on them and then we store the data when there's an update function Fields operate at the database level and they're super efficient they're operating on the row level and they they do real-time calculations many of the same things we do in function field in Formula fields we can do in function Fields although function Fields do have a little bit larger library of of operators that they can call performance is phenomenal um you're not going to notice it it's calculated on the Fly the data is not stored and it does not require admin access this is the the biggest thing we're going to see between function Fields calculated fields and formula Fields function Fields do not require admin access it can be defined by an admin and you get and there's a few restrictions that are lifted when you're an admin there's basically very few rules when you're an admin but I can Del I can give somebody the function field admin r and that will allow them to create function fields in report designer uh using UI the biggest disadvantage we have with function Fields is that they have limited functions there's only limited operators that we that we can use we're not executing Uh custom JavaScript so with a calculator field I can just do whatever I want um good bad and ugly I can do whatever I want formula Fields I can do a lot function Fields it's a little bit less but it's a lot of very common things that we want to have in the performance is unmatched and we also are not storing them physically so if we are doing a lot it's not going to use any database base they look and behave like normal Fields but because they're done at the database level we can query on them um we can Nest them but the we don't have to store them all right so this is the function field Builder and Report designer so I can go into report designer and I can uh I give it a name I give that field a name a label I talk about what type it is because we are putting it is getting put into the data dictionary even if I'm not an admin it's flagged and and sandboxed in a certain way The Operators that I want to have I want to use I have a choice of operators which we'll show in the next slide and then which field I want to have and again you can Nest these U but as we go through uh we have lots of options so whe whether we're adding or subtracting we have normal math functions that we can call but a lot of really useful ones are coalescing so I can take field A and B and C and just give me give me a if it's not null otherwi give me B if a is null and B is not null and if a and b are null and C is not null then give me C or I put a constant in there as well so I can say you know no value here really really useful things that I could have created a business rule for or a flow for calculated field for in the past um name I think could be done this way uh pretty easily uh but we have coalescing we have Conca um I use concatenation a lot for display names I take a number Dash the D short description and create a display name so that I know my reference field to kind of get that full description but I don't have to store I don't have to store and I don't have to sync the short description at any time but I see all the when I'm seeing it displayed as a reference I see the number and the name or some other field that comes through um date diff distance Square these have been added over time as different use cases come up um we'll see there's a lot here not everything but a lot um and whether I'm doing positioning and sub strings this is things like parsing the example for those of us are old enough parsing out uh different octets in an IP address or splitting actually used split table names in service now we have table name. field name I can use these and Nest them to get just the table name if there is a table name or or table. field very very useful you see the examples here these are all in the documentation as well we can use constants we can use Fields when you're in report designer and you only have the when you're in report designer you cannot dotw walk there's a security constraint so that you're not allowed to dot walk if I'm admin and I'm actually uh defining this in the dictionary I can dotl walk so dotl walking is possible uh but restricted because of security all right hey Adam real quick um as you're talking about the function fields and and you may talk about this later you may show it later uh I don't know we've had two that have one's come in in the chat and one's come in in the Q&A that are exactly the same and they're talking about the date the date div and being able to calculate the duration of a date and now like a previous date like a created on on and now um I know it's very specific I don't know if you you're demoing something like that but is is that possible um I believe it cannot be done in report designer because it report designer validates it and now I believe is not a supported function but it might be an unsupported function that's in there I'd have to go check um I'm fairly certain admins can do it uh can can put in now that there was something to have into that but I'll have to check I originally so function fields and I apologize Thomas correct me if I confuse function and formula Fields because it's confusing when I say it but function Fields what we're talking about now uh came out in Madrid been out for a while originally you couldn't do now and I know in report designer you're still you still can't pick it out uh but I believe the admin can do a created time and a date dip between created time and now uh to get that and if you're if you're using these then I'd love to hear from you um whether it's in the community or through your account team about use cases you'd like to have um you know reach out to to me to Thomas and let us know those use cases so we can document it so we can get them added these things can be added they can be expanded uh by us but we have to add support for it and these were things that we thought made sense um and if there's more use cases that would that would unlock more value for you if I can get you using function Fields over formula and calculated Fields then I want to unlock that for you because the performance is so much better um and we don't as we saw between calculator fields and formula Fields really similar just how complicated it can build when we get to function Fields there's not many disadvantages other than it's limited by what we can build into there um and the function Fields do not execute JavaScript JavaScript has to be executed on the on the application node versus the database the reason we get this performance boost is because we put it back into the function field which or into back into the database which allows us to run this operator on Records On hundreds or thousands or hundreds of thousands of Records whereas if we're doing that on a uh a function field we'd have to read all of those records and move data Bunch back and forth and run all these calculations and I don't want to run JavaScript 100,000 times but I can run a concatenation in the database 100,000 times I don't care databases are very good at that um that's why there's a difference uh we give you the different options because there's different use cases and you have different needs and we want to make sure that you can do those different things uh as appropriate um and I saw there was another question about flow designer so function fields are Fields the none of service now actually knows that they're function Fields outside of the dictionary um the're readon function field actually all of these things we're talking about are readon uh because they're they're doing the calculation but absolutely you can read you can do this in flow designer you can use this in flow designer you can use a function field flow designer in reports wherever you want it to be um you can use them in performance analytics as well uh there is a note as we go through this um hopefully this will get resolved in in a future release the function Fields if you're not admin are only definable in report designer but once they're defined they can be used across the instance um so they're flagged appropriately but they they're used across the instance so if you had to find it report designer I can then use it in flow designer just make sure you restart flow designer so that it picks up the new the new Fields uh okay um but they're used wherever you want to have is there any anything else that we should answer about function formula or calculated Fields before we get the formula yeah I mean there's another one it just says if function fields are not physically stored does that imply that they can be used in reports but not in a query no they can be used in a query uh they're calculated on the Fly Glide record doesn't know it any better um they're just they're they're not physically stored so if I were doing my display name I mean generally I'm not super worried about this space but if you use a formula field or a calculated field you we are physically storing those in the database it does consume space it's probably going to be Tri unless you have a huge table but it's there if I am doing a function field it's not physically stored it's calculated on the Fly again performance is you can't notice the performance issue any performance even though it's doing that calculation because they're um very very very very optimized and you can use them in a query and they will be correct they absolutely will be correct there's no time issues whereas the calculated Fields again what what it is right now and what it was in the last update there can be a Delta specifically with with time if you have a calculated field that c calculates how much time is open you can't query that you can and you get the wrong answer you'll query and get the wrong answer so calculator fields and formula Fields should never be used in a in a um query condition and if it can be used in a query condition because it's that stable like name then you should probably go with the static field and business rule right because it doesn't change that much so a calculator field formula field is good for display but not for query function fields are perfectly safe to query on um absolutely they they are they are my favorite if it can do what you want you want to use a function field if it can't then I probably go formula field and if it that can't then I probably go to calculated field because I can run any JavaScript that does anything I want it to do but every step you go along that way the more you can do you know the more robust the the processor has to be and the more likely you are to have a performance issue or more Pro most calculator fields are fine but you know you can put yourself you can dig a big ditch for yourself okay great thank you Adam yeah and there are function Fields there is a limit of 20 per table through report designer 20 active 20 active fields in report designer um you can have more if you deactivate them I believe admin could add more if you wanted to I don't think we put the restriction on there um if you have use cases for it let us know for some of these things uh we have we put some limits in place um because we want to make sure the performance is always good and and that we don't uh injur ourselves to come through um if you have a need for more than 20 calculated Fields 20 function sorry got that one wrong if you have a need for more than 20 function fields on a on a table active at the same time um again reach out to your account team to talk to Thomas talk to me I want to understand what you're doing and then we can figure out if there's a way to unlock it um a lot of times we are able to do it with some system property but I just want to make sure we have a discussion of what those use cases are and if we need to expand it for everybody then we'll do that as well okay great thank you all right so now I'm going to start the presentation that Thomas asked me for why you're all here so we're going to talk about formula indicators so we're going to start out with the with the good bad differences between those as we spent time talking about the other three um the first off for formula indicators is it executes JavaScript and exe executing JavaScript is a double-edged stor we're going to go through some use cases that they're real theoretically I guess um use with caution uh when you with great power comes great responsibility if you're writing JavaScript and you're doing really crazy things it can cause a problem it cause it will do what you want but that takes time so be conscious of that we will talk about that as we go through the examples big difference between for between calculated formula and function Fields is they operate on the row level um and and have possibility to dot walk or not formula indicators operate on Aggregates they R operate on on performance analytics indicators which are sums and counts and mins and Max normally some and counts are the most common ones but they operate on that aggregate grouped by something whether it's a total or a group by that gives me different it's very different use cases to come through and we run into some situations about averaging averages uh you know we have to be careful about that but I might be using a mix of formulas and uh formula fields and formula indicators but understand what you're doing am I operating on the field I'm transforming which might be a cat a string length something like that um or I'm or am I operating the aggregate I want to take the total number of uh the total amount of up time divided by the total amount of time there was the total number of incidents divided by my total number of Agents those are Aggregates right um the data is not stored it's not materialized uh for for formula indicators it's always calculated on the Fly very very fast and again there's there's some limits on how it comes through on what it's derve but um it's calculated on the Fly and it's you can use it with all the standard performance analytics features I'm not aware and some of the other panelists might correct me if I'm missing one I'm not aware of any performance analytics feature that can't use a formula indicator um it's built this is a very very very normal expected use case for performance analytics to use formula indicators that are built on automated indicators where we're collecting data and running a query again disadvantage executes on JavaScript so you can absolutely Sho yourself on the foot I'm going to give you directions on how to do that in a moment okay so why do we use formula indicators we talked about the data Transformations uh for the other phes and the field level it makes sense right I want to concatenate first name and last name I want to get the length of the short description U because I'm trying to do some data quality things I'm going to concat concatenate these fields or split these fields or whatever it's going to be formula indicators different so we use formula indicators it's a powerful way to gain more insight on existing indicators I already have the total number of cases and I I can get the total number of agents and now I'm going to not make the viewer think uh as with ASP me a decade ago and to make sure that uh it's really easy to see that comparison that normalized number which I can compare uh across groups across regions across time I can compare myself over time am I getting better or worse um and it gives us those meaningful kpis so one of the quotes that I like about this is without a standard there's no logical basis for decision- making or taking action that's that that's fundamental to Performance analytics we want to have those measures but the number of open cases just doesn't give me a lot that sometimes I need those absolute numbers but I get it I'm not saying they're worthless but there's so much action that can be taken when I normalize that data and compare I need to have the comparison whether it's me from last month me from last year or a different group when I can compare and contrast the different teams I can find out which teams are are doing something better or worse and figure out how to help the teams that are not performing as well do better help to bring up the middle to the ones that are doing better see those anomalies I have to use formula indicators to see that good targets come from good indicators and good indicators are are formula indicators when we talk about performance analytics and and we under and we're trying to look at does this customer do you get it do you understand what we're trying to do we look for formula indicators because that is the icing on the cake and I like icing like caffeine and sugar great things icing doesn't have caffeine I understand that but if I drink if I drink a soda and some icing it's awesome okay uh cases when formulas are indicated so to calculate multiple indicators and average two indicators uh very common you see those out of the box uh to calculate um uh to see operate or we want to we want to look at all the standard things again incidents uh to agents the the percentage uh or the average number of reassignments how many reassignments are happening per group really really interesting to see what's going on and we want to measure the Gap a lot of times we want to meure the Gap to Target we're going to set a Target and then we're going to measure how off our be I have an SLA attainment goal of 98% how far off am I I don't care about a specific incident or how many you did how many you met or how many you missed but our goal is 98% so that we're scaling appropriately formulas help us see that U here's the example you'll see this out of the box everybody has this I think um about the incident backlog growth so I have my number of incidents coming in minus the incidents resolved gives me my backlog growth and I'm seeing how many more incidents are getting piled up number of new incidents can be misleading clothes can be misleading but how many are in there now I actually might do some more things about how many are aged over 14 days um I actually will look at this later about adding percentage growth on here so percentage growth is displayed that 19.8% is down down this a good good number it's down 20% but maybe I want that that 20% not just displayed here but I want actually that to be my target I want to reduce my backlog by 10% quarter over quarter over quarter for the next year because I have a problem with backlog we can use formulas to derive that without having to do without having to collect a bunch of data or do math on the fly but as we're going so what are some other meaningful kpis you know these are metrics so indicators are indicators we don't actually use the word kpi for PA in performance analytics because we collect lot of the metrics the number of slas breached the number of open incidents the number of incidents aging the number of open over five days number new number closed these are metrics they're numbers super important you you can't have kpis without metrics but what we want to look at is the other way to build this the meaningful kpi is based off of these so the percentage of attainment if I have the percentage of attainment then my Amia team should be doing the same as my apj team should be doing the same team as my America's um if I see Amia is has a much better SLA attain mment I need to go ask questions why is that what's going on um the number of open incidents per CI supported I like this one um because different teams support different numbers of things number of servers number of devices whatever it's going to be all my CIS but if I can start looking at the number of incidents per like Ci class per group maybe we'll see a trend this something's going off on how we're maintaining servers because we have a lot more incidents um in apj with our servers than we do in the Americas and then percentage of Aging not just the number but the percentage that are percentage that it's aging again am I going to see a problem I'm not doing as well as I did last year or this team is doing better than that team or p1s are big problem and p3s are fine well that concerning to me too right I want to get those p1s addressed so that percentage really important and then the backlog growth like we talked about important notes um for formula indicators they are processed on the server side so the process when you get the data to come through as long as they're kind of normal calculations it's fine um I'm worried more about this for calculated Fields because they're doing row at a time so if I have 10,000 open incidences open incidents um then I'm having to do that calculation a lot for in this case we're only getting the one score per day per indicator so it's generally not a big issue um but if I was looking at two years of data um and I have to pull back a bunch of calculated fields in an index field and I'm on a workbench that has tons of indicators be it's something to be aware of of not concerned about in calculator Fields I'm more aware and concerned but they are processed on the server side they're calculate when requested they're just sitting there U normal jobs are do calculate them um if I'm calculating targets or thresholds or breaches or any of that stuff they're calculated then so it's not always when the viewer views them so there are some scheduled jobs that look at them but they're calculated when I ask for them um full access to service on libraries this is what we're going to talk about there's a lot more than you can do than what the basics are when we ship them out a box we're just doing a minus b a divided by B sorry normal most of the time but we have access to everything good and bad access to everything that service now JavaScript has access to you can call functions from script includes when you get into some complex stuff I'll actually put I'll create a script include that's client callable and then I will use that in my function field uh to do some of this transformation you'll see I didn't do that in these examples so you can see some of them get a bit long but I can write a script include to support my PA functions if that's that's needed where I'm just passing in the previous indicator values and then the math and whatever happens in the background can happen in a script include be very aware of performance um again normally it's fine but if you start doing crazy things then performance can can go along with that also be very aware of performance and if you missed the first two be very aware of performance um be aware be conscious uh we'll talk about some of the things that are in here when it's a simple calculation it's fine but if we have it do some of the the advanced use cases we have to make sure that we're not showing too much on the screen at the same time and not having it asking it to load up 100,000 records in a second right and do a bunch of calculations all right let's talk about some of those use cases uh that come through I think I'm doing okay with time right now yeah we have some very are you talking about your use cases or the ones that are in my use cases I got my okay yeah go for it all right and we'll get the question so hopefully we'll we'll wrap this up in a few minutes so we can talk more about the questions you have um so i' like to bring this one up because this one uh there's no extra quering there's nothing out there this is still very very performant I have no concerns about using this one but what I'm doing here is I'm normalizing the output I'm rounding um and in this case I am rounding to the 25% um I had something that a little too variant there's too much variance in what goes in um and so I am rounding the percentage of incidents resolved by the first assignment group um the times four round to the four thing this gives me quarter points I only want to see 97.5 97.75% point88 super useful um to do something like that whether you're rounding to a whole number um you can you I take back you can actually do that uh with just formatting it in the indicator but if I wanted to do more than formatting I want it to be half points or quarter points we could do that with math.round we have access to the full JavaScript library also useful for Min and Max which I'm missing an m in here for but Min and Max because I never want to show it Go below zero I have some formula and it can go below zero we have options for the forecast but we don't do that an option to say you know change the number well you can change the number full access with to the math functions right to everything JavaScript has access to uh the next one I should actually put this first this is the normal one right this one shouldn't be shocking everybody knows this one we do it all the time you have standard uh arithmetic operators so not just functions in JavaScript but uh operators multiplication division subtraction addition all of those um modulus whatever you need to have we can do that uh these are running again on the Aggregates so I'm putting in I want the average unassigned duration of resolved days uh for the breakdown seven or or with the time series 7 Day running average divided by uh the duration of assignments of resolved incidents times 100 give me a percentage so when you see times 100 it's because we're dividing something out but I want a percentage so we multiply it by 100 you have access to that normal one everybody sees this but we can do also we can round this we can run whatever we want to do with math or any other JavaScript function um one we get asked about occasionally to come through um is put a constant in so normally I I normally I'm going to look for a manual indicator or an automated indicator that's collecting the normal date if I was doing an fx rate or something uh to store it before I want or a manual indicator often I'll use targets because my targets do change over time constants like pure constant is odd to me because almost all constants do change um but if you have a real constant you can do that in performance analytics super simple I'm going to create a formula um and put in the constant as the last line um the note here and I I'm not aware that we have a bypass for this but this is one that catches a lot of people off guard um there's an optimization that if there are no indicators then there must not be a score um which is not always true so in this case I'm going to put an indicator in there uh the curly braces mean don't ignore all breakdown so it's very clear it's going to look up one score uh from the scores table which is very very very very fast it's all indexed and looked up and then ignore it and just tell me 50 in all the formula indicators the last line is what's returned the last line is what's returned so you'll see um either there is only one line or you'll see we'll see calculation with res or in this case it's a constant of 50 but if I want to have this this will give me a constant L of 50 which I can use however I want to use it the next is sure um show or hide um scores based off of the date and I have um manual scores it doesn't the manual scores doesn't matter um this is where I don't want to show any scores before a specific date process change something happened I have the scores and I'm not willing to delete them yet um I can put a function field on there that will delete them so in this case we're not going to show any scores that are before before March 1st of this year if you're watching the recording we recorded this in 2024 so uh it's not going to show any scores before them they're just uh null there there's no value I don't have a last line so it's null it's useful sometimes um I'm hardcoding the DAT in here which might be okay I could also have it look at a a system property I could have it do a query but if you're doing a query it means every score it's got to do a query so you got to be careful about that I might just put my constant in here but it is something where I don't want to show these scores or to this group we don't want to show them but we're going to keep them we're not going to delete those old scores um I can use score end score start uh there's information about there about the docs we use these in in performance analytic scripts score end and score start they're the same in in um uh PA formulas you also have score and score start um normally I actually use score start I think there's some weirdness that it comes in here but they are the date that you're viewing the date that you're viewing so um it's comparing is this score for this aggregate on or before March 1 or on is comparing it and then if it's if it's after it'll show it um but score start and score end are often used if I'm doing some date comparison this is the use case I see most often and then I can just use a new Glide date time and and the score end is is a Glide date time so I can use the compare function compared to all right this is the one it exists you're seeing it you can do it but again be conscious of when you're using it and that you're not using it overly this will say when I get a scoreb go back go in and query the FX rate table for USD um I believe the base yeah the FX rate is based on Euro that might differ per instance it used to always be Euro um but in this example I'm collecting data on Euro and then I'm translating it on the Fly based off of the FX date for that day FX date now I could do some collection and maybe I can get around this and put it all PA scores and make the FX rate a PA score I could probably do that there's probably a better way to do this but I didn't um and in this case I'm getting the latest FX rate for that score that might be loaded afterwards that's where you can get into some weird stuff it's just getting the lace one and and one if I'm looking at 100 scores and I have to run this query 100 times ah whatever I can do 100 queries pretty quickly but if I were looking at a a just a ton of data points or I'm layering on index scores that's when we can start seeing some performance issues because it is running this query based on every different score date but I can do it and sometimes there's absolutely valid use cases to do this so if you see at the bottom we're getting an indicator the percentage of incidents resolved by first assignment group um why that is divided by rate that's probably not a super real use case but I'm able to run that query I'm able to get a value and then use that in my indicator this can do anything whether I'm looking at up an fx rate whether I'm calling a a rest I'm using integration Hub and calling some web service to get some value you can do those things often I'm going to want to materialize it just like we had with calculated fields that we can do them on the fly but I'm probably going to use a flow and a static field maybe I'm going to create a table and put it in there but there's some cases where this is not used very often and I want it to be dynamic and I want to create a new integration and do a bunch of stuff so you have you have this option if that makes sense okay some important notes um this allows you to um allows you to visualize and set targets where you previously couldn't so we saw in the example before where we had a 20% uh decrease in backlog well I actually want that 20% decrease to be my metric and I want to Target that I'm giving a blanket requirement to all of my teams to reduce their backlog by 5% each quarter that is my Target and instead of having to go look at that I can create that derivative Target using formulas so I can measure your backlog ction um and then I can set a Target on that on that formula right super super powerful uh if I just do the calculations and an Excel we're not able to leverage everything we have in in service now and uh be careful this can add complexity right writing layers so just be careful be aware the next part we're going to get into um that comes through is using PA formula utils so you'll see this when you're doing a formula you can browse a method and we have several methods that are specific to per performance analytics that help you use other data in different ways so whether I'm getting the global Target for something um if I have a breakdown you set targets for each of the breakdowns but I want to do that I just want the global Target so I can say give me the global Target I want EV the global Target for everybody is you know 98% SLA attainment I don't need to set that for every group but I do want to see my comparison so we're able to do those things um there's several of these things we not go through them all but if you browse method you can see them and it'll tell you how to call them um when we do that you select the method and then it'll give you the the properties that you want to call whether you're putting in um a a hardcoded date with the date picker or putting in score start or score end or whatever it's going to be you'll Supply those values it'll tell you how to use it um the next example I have now um to go through we're using those formula utils to get the Gap that's what we were talking about before where I the Gap is what's the difference between the Target and the actual that allows me to build index index index scores um we used to do a lab on this at knowledge um I think we still have might be still still be something in uh now learning and there is an old Academy session on this we spend an hour talking about this use case um but to go through it's really important that I can start uh having an index score so it is my SLA attainment plus my backlog reduction plus my cat cat gives me an index score that I can combine those groups with waiting that I want to give them to in this case they're equal waiting but I could do other waiting too too that this is 25% 40% um and 35% hopefully I got that right when we do the recording if not Thomas will edit it out and fix it for me um really really useful and Powerful to do index scores using formulas and index scores can get really interesting when we're doing gaps and doing a lot of other things uh between them so I want to as long as I'm setting the target um you know every group in this case we're not hard-coding Targets in the in the old days we hardcoded targets but in this case we set your target for your group and we're measuring you based off of the index of your actuals to your target which can be weighted or which are different values for everybody but the index of improvement is the same all right um and we can look at at setting uh setting the target to compareed to itself so backlog growth this is the formula we use for this one this is probably the most common um function that I use in Formula Fields the most common um uh PA TS method which is comparing last period so I'm using uh score start and I build the first of the month I'm just I'm using score start so it's taking whatever day it's showing that's the score start I get the year of that I get the month of that and I make it the first and then I subtract the day there's not a subtract method there's an add so I add negative one day which gives me the last day of the previous period and now what I'm getting is the change percentage between today score start and the last day of last month and that helps me understand between last period and this period what was my growth so if I want to look at back growth that score can do it this helps me do more time manipulation it can be a little screwy up here so you're going want to test it you definitely want to look at the docs this example and most of these examples are in the documentation um so go take a look at that but this is how I can start doing things like comparing my reduction month on month year over year I can see it in performance analytics in in the analytics hub uh kpi details I can see it on a dashboard but when I want that to be my target we need to build a formula so I can set a Target on that itself so I can look for thresholds in that itself um and then this gets a little wild this one I I this one always makes me a little cringey when I look at it but we can use the formulas to get the current breakdown to get the current element and make some adjustments that come through so it might be um in this case we're filtering out p1s um I don't like hard cating IDs ever or anywhere so I might set some system properties which I have access to because it's fully JavaScript I can use a gs. get property um but I can do some manipulation from things the reason this is here is because we know there are use cases that people export to Excel and do weird stuff I don't like it I don't recommend it but I know we have to do it and so we have a lot of functionality that we can put into the formulas this because it's getting the breakdown ID the element ID it has this data this these are not queries that it's running a bunch of stuff on be very very cautious if you're running a Glide aggregate or a Glide record in a formula or in a calculated field um but manipulation Through Math um looking up another indicator with some limits getting the Gap all that stuff we we've optimized that as much as possible um and doing these Al this some of these alterations um I I do want to question why we're doing it but again I know that there's cases where we need to do it and I'd rather do it here and do it in a formula and have it audited in my update Set uh in in my system log that we did it and who did it then export a bunch of data to excel to to Let It Go um I am going to wrap up a little bit and we'll get into some more questions um there are a bunch of resources in here so when we get this posted um we will get these these resources out here um most of what I showed you is in the docs so we'll point you in the right direction for what's there um there's a formula indicator uh course in now learning that you'll probably want to take along with the other formance analytic ones that that come through there and then again get lots of documentation that come through and with that I want to see if I can answer a few more questions uh before yeah most of the ones that are there are very specific use cases for formula indicators so um if you want to talk to those yeah right which is kind of tough because you may actually have to show them so we may have to uh maybe put th those questions in a blog or something with the answers but go for it um so uh one of them comes in um about a use case where we're where we're building a formula to use a quarter period weekly average to subtract the weekly average from all the incidents plus the what comes in yeah you could do that I I can totally see being able to do that as long as you have the indicators and if you're using the form indicator say give me last month um I don't see I think it's definitely possible to do getting the right order of operations is is a bit tricky but you're going to use the formula utils the pa pa formula UTI as API to get the previous score so when I'm looking at July 1st I'm going to get the score from Q2 that's well every time every month for three months is going to be looking at q2's number so you build that date like we saw and then you'll C you'll add and subtract based on what you want to have I get worried about some of the complexity of these things um because you really understand when people are looking at it do you really understand what it is I understand SLA tainment but a bunch of alterations but again I see it is I want to compare our performance to a specific date like we changed our process this is a few years ago I know we changed some process on uh July 11th so we had 7-Eleven hardcoded in because we don't want to ignore everything before then and that was the Baseline um or every release we'll do this service now that we're comparing on a given release date that's the comparison so we're comparing release over release um which is not quarters it's not it's not directly six months all the time um but we have a date in the system that says this is this date so you're comparing the growth from the release date and then when the next release dates you start comparing to that release date um so I I think that's definitely doable and I don't I don't have any performance concerns about it um so the how long things have been open to we were talking about this about the now um for a function field um I believe an admin can put in now I think that's real um it is not documented which probably means it's not supported um but it's worked since Madrid or it's not going to work one of the two um I'll continue to pass that down we have this recording we all pass this back to our um to our product management counterparts on the inbound side to go hey we need to support this and and I that is my favorite use case because it's something that always has to be right a calculated field and a formula field will not be correct if you query on dates on the on how long it's been open so you have to get into you want to display it's been open for four days but you have to query on create a date and that is complicated right it's complicated for my users so I'd rather have hey it's give me everything it's been open more than four days because I want that's how I want to see that's what we're talking about but report designer will not let you do it but I believe admin will and is it Poss so Thomas tell me when we get the boot we need to wrap up um to display the average of incidents over a period of six months yeah okay so great one in here is it possible to use formula indicators to display the weekly average of incidence over a period of six months so if you're building the dates you can do some of that stuff um so one of the things things that I I had is I wanted to get the um I I did the easy one I'll tell you my easy one is give me the last give me the backlog what was it on the last day of last month which is hard in a lot of places if you're trying to do 28 30 all that stuff but if you do if you it's easy to calculate the first day of the month like I had those constants minus one that's it but some of the things that I'll do is average um whether I'm picking the 15th of the month or I want to get a monthly indicator um I'm going to I'm going to take last month I'm going to get a monthly IND I'm going to use a Time series to give it monthly to get that score and then I can use the PA formulas to say The Benchmark I want to have is the monthly average the monthly average of of backlog every day not the last day not the first day not the 15th but the average so you definitely could do some of those things um and have a six- week rolling period you can do some really really creative things with the get dat get get Target um and looking at score start you know what day you're at and it's not today it's the day the score is from um if you practice and go through some of the things you'll get it wrong a couple times um because you are doing a lot of 3D chest type stuff um but it's it's very doable and that's why we built them that's why we built that formula indicators is to be able to do those types of things okay another one about the First first day of the month so I think we went through that example um after this question was asked because we're getting into it but yeah getting the first day of last month or the first day of this month super common super easy very performant um you it's three line three line formula because I like I don't like to cram everything together that example is in the documentations documentation so you're just going to get score I think it's just get score for this indicator you'll put the indicator name in which is the same one you're looking at and um the date right and again you'll build that date with just get score uh score start current year current month and then the first okay maybe one more and then we'll close out if you want um okay I'm going to answer an easy one that comes through does math does math.round alter the standard Precision functionality um setting on the indicator no it'll still that for formats the number the reason I use math round is if I want don't want it to be a whole number so the Precision indicator on the indic the Precision value on the indicator uh is going to give me um how many digits of precision to get through or significance to to see but sometimes I won't be able to do that especially if I'm in the millions I want to round I want to have some Dynamic rounding so if it's in um you're going to show me whole numbers but if it's over a thousand just round it to a thousand right if when I'm down in the low numbers keep it low you know I want to see the difference between one and two and three but when I'm I don't need to see the difference between one and 101 and 102 and 100003 so I might put some Dynamic routing in there or rounding uh to format based on this number this is what we need to do okay great I think for I think that one Jane um Marta I was going to see if Marta could get to that one I mean if you want to try to knock out that other one real quick and then we can close out or we can point him to the to the community Adam um the last one of the formula indicators yeah yeah okay I've tried creating formula indicators using Glide date um displayed in a single score widget I'm trying to display a timer um this is just days to go date days um if you put that one in the community I know we've done that uh to come through the number if I have a go live date of October 1st and the number of days until then um I I take it back I when you wanted to do something like that I don't know that it will be a formula indicator I think that might be a formula field um to come through because it's at a row level if I have a specific release so something like that you might want to look at with hopeful it's actually with now this this goes back to can I use now in a formula field so I have one record for the release we want to get it you can do it with a formula indicator where you're basically going to group by the release and pick out what's in there um and you end up with a Min so you do an aggregate but you're like give me the Min minus current you can do minus current date so if you're having problems with that put that in the community tag Thomas I me we'll take a look at it on what's going wrong my guess is you're like one you know one line off or one thing that that's off but that's that's achievable to have in there to get that countdown timer whether it's a function field or formula field I mean that's not a that's one record to look up so you might want to look at a formula field um function Fields probably work as well and then we can do it with indicators but the first formulas we had we had calculator fields which have their issues we had formula indicators but with for function Fields you know we don't have to cram everything to Performance analytics we've tried to make sure you don't have to cramp you don't have to use performance analytics when you don't need targets and performance analytics there's no target for the uh number of days to to countdown so let's see if we can do it without performance analytics and having to do a lot of extra stuff awesome awesome great so uh Adam thank you very much I love how you went around and finally got to what I asked for but I think all of it is uh pretty awesome and it's going to be great to have a course in our library of ourmes and and people will definitely use this as we put it out to the community so thank you very much and appreciate all the different examples so really good stuff thank you for all the questions and panelists as well so real quickly let me hit these things so obviously this will be um make sure you're checking out the community if we didn't if you had another question question uh and you weren't able to ask it today for whatever reason um hit us up in the community you can tag myself and we'll definitely get you an answer um this will be out in the community like I said probably tomorrow but also our new uh and previous stuff is there uh six years worth of Academy so check those out make sure you're utilizing now learning uh I hear just simply search for analytics and it's constantly being added to so check that out um make sure that you're looking at our Cur a platform analytics uh videos and and PowerPoints that are out there in the community here's the link for that uh this will will really help you in your journey in platform analytics and also gives you the ability to reuse the videos and PowerPoints within your organization as a whole to train up your users on using it as well uh but in a couple weeks we're going to talk about talk about exporting dashboards in uh or exporting visualizations I'm sorry in platform analytics so come back in two weeks another great session once again thank you Adam and Patty and Marta and Tara who has already left we appreciate it we'll see everybody in two weeks thank you