10 Replies Latest reply on May 13, 2019 8:43 AM by Eric Liszt

    Calculated Fields Need Definitions

    Crystal Jones
      Share This:

      So I have been high and low across this forum and the Footprints documents and the internet at large and I do not have an answer. I have a TON of questions regarding the calculated fields. Right now I need clear definitions for the following calculated fields.

       

      • Time: Activated to Resolved
      • Time: Activated to Closed
      • Time: Created to Resolved
      • Time: Created to Closed
        • 1. Re: Calculated Fields Need Definitions
          Rob Farrington

          Hi Crystal,

           

          This is what I understand these to mean. When you work with the Lifecycle, you place statuses in Pre-Active, Active, Resolved, and Closed "states". Therefore, the below would be the following:

           

          • Time: Activated to Resolved: When placed in an 'Active' status until moved to a 'Resolved' status
          • Time: Activated to Closed: 'Active' status to 'Closed' status

           

          When a ticket is created, it can be in a 'Pre-active' status, which doesn't account for time such as idle/aged *during* the life of a ticket. So, in essence, when a ticket is new and they hit submit:

           

          • Time: Created to Resolved: Once submitted, or 'created', in any status until placed in a 'Resolved' status
          • Time: Created to Closed: Once submitted, or 'created', in any status until placed in a 'Closed' status

           

          I hope this helps!!

           

          Rob

          • 2. Re: Calculated Fields Need Definitions
            Crystal Jones

            Thanks for confirming this for me. It does help, but it also confirms that there is something VERY wrong with how the system is calculating these fields.

             

            For example:

            Except for cancellations (which close immediately) our tickets remain in the resolved status until they are closed by business rule three days after being resolved. Activated to Closed should be THREE DAYS after Activated to Resolved. Currently these values (when it does calculate) are the same.I would expect to see two different values in these calculations (except for tickets that are cancelled as they are closed immediately), and we do not.

             

            The Created to Resolved and Created to Closed should calculate similarly. Since we don't have "Pre-Active" statuses in our workflows, I expect that these values will be the same as the Activated to Close and Activated to Resolved calcs.

            • 3. Re: Calculated Fields Need Definitions
              Rob Farrington

              Absolutely agree, that should be the case. If it is not calculating that way, then it is not working as expected. I've seen you and John Ossman discussing this in another post as well and agree that BMC should be put on notice to determine what is going on.

               

              Please keep us in the loop because this is important to many customers I'm sure.

               

              Thanks!

              Rob

              • 4. Re: Calculated Fields Need Definitions
                Crystal Jones

                Absolutely! This is a HOT topic for us right now and I am in need of a solution as it appears that there is a defect in this functionality.

                • 5. Re: Calculated Fields Need Definitions
                  Vern Meyer

                  I have a work around that can be implemented two ways:

                  A: Replicating the OOTB lifecycle reporting (grouped states)

                  B: Specific status reporting (granular to each ticket status)

                   

                  Notes:

                  - I am running a rather old Version of footprints (12.1.05), so there may be discrepancies.

                  - As implementation is basically the same, I'll cover the steps for both of the scenarios.

                  - For the purpose of this instruction, the grouped states in scenario A will be defined as Pre-active, Active, Resolved and Closed.

                  - Both Scenarios require a workflow, as workflow rules enable the "On Enter" trigger

                  - The data capturing fields do not need to be applied to them form.

                  - Consider making this fields SHARED. This will allow you to inherit fields and pass along information easier.

                   

                  Create the data capturing fields

                  A: Create Date-Time fields for your lifecycles (Pre-active, Active, Resolved, Closed)

                  Example:             Singular Name: Pre-Active-DateTime

                                                  Field Type: Date-Time

                                                  Permissions: Optional

                  B: Create time-date fields for each status

                  Example:             Singular Name: ApprovedOn-DateTime

                                                  Field Type: Date-Time

                                                  Permissions: Optional

                   

                  Create the data population workflow rules

                  NOTE: The only difference in this step is the field you're taking action on. In B, This will be specific to each state. In A, You'll have the same rule exact rule in the statuses that are part of the same lifecycle event.

                  A: Create a rule for each status in the workflow:

                  Example               - Trigger: On Enter

                                                  - Criteria: (none) or (something that’s always true like TicketNumber=AnyValue)

                                                  - Actions: Set Field Value: (Field) "Pre-Active-DateTime"; (Value) [updated_on]

                  B: Create a rule for each status in the workflow:

                  Example               - Trigger: On Enter

                                                  - Criteria: (none) or (something that’s always true like TicketNumber=AnyValue)

                                                  - Actions: Set Field Value: (Field) "Approved-DateTime"; (Value) [updated_on]

                   

                  From here, I export the raw data via Saved search, then Service Analytics. Once exported, I calculate the timeframes within excel. This can be done in service analytics to some extent, I just prefer excel. Once I have the excel report configured, I remove the raw data and save the file as a template to make the report easy.

                   

                  I can go further with the excel logic if you'd like!

                  • 6. Re: Calculated Fields Need Definitions
                    Crystal Jones

                    Vern Meyer OMG you are the BEST!!!

                     

                    Yes if you would be so kind as to share the Excel logic that would be awesome. In the meantime I am going to pass on your last post to my FP Admins.

                    • 7. Re: Calculated Fields Need Definitions
                      Rob Farrington

                      Vern Meyer this is awesome!

                       

                      Seems like a lot of work for OOTB functionality expected of the product though....

                      • 8. Re: Calculated Fields Need Definitions
                        Crystal Jones

                        Rob Farrington I agree..

                         

                        It would be NICE to hear from BMC (still looking at you Eric Liszt ) It seems CRYSTAL clear to me that this is a system defect, and one that has not been addressed for quite some time (perhaps since the launch of v12.x?). I'm happy to be proven wrong here, but I don't think I am. A fix is LONG overdue as these metrics are a BASIC KPI that one would want to get from their ITSM tool. Frankly this is only giving those who are beating the Jira drum in my org more ammo to justify the move from Footprints to Jira. All they need is one really well written cost benefit analysis to demonstrate how the cost to move to Jira will outweigh the costs of keeping a product where basic KPIs are not available out the box. *shrug*

                        • 9. Re: Calculated Fields Need Definitions
                          Vern Meyer

                          Feel free to have your admins reach out if they have any questions. My post was pretty quickly thrown together between meetings yesterday so there may be some confusion that I'm not seeing.

                           

                          I completely agree. Working around failing OOTB features is super frustrating, especially since we hear nothing from BMC. Regardless, we need the data no matter how low priority it is for BMC. So we stick yet another pin in the FootPrints voodoo doll and figure it out ourselves.

                           

                          Okay, so for the excel logic I'm going to focus on A (Time: Activated to Resolved). The same works for B, it's just more granular (Status: In Progress to Complete). You can ignore the CAR prefix on fields as it's just one of our specific processes.

                           

                          Example Report; Tickets resolved last month with time Activated to Resolved.

                          - Build a saved search:

                          Example:                 Title: Resolved tickets last months

                                                          Search for: (Field)Resolved-Datetime, (Condition) Range, (Value) Last Month

                                                          Publishing Options: Include the Advanced Search in the list of data sources for Service Analytics.

                          - Build a service analytic report:

                          Example:                 Title: Tickets resolved last month with time Activated to Resolved

                                                          Display fields: (At least) Activated-DateTime, and Resolved-DateTime

                          FORMULA COLUMN(S): These get the raw data in minutes for use in excel.

                                                                          Name: Minutes Activated to Resolved

                                                                          Formula: datediff("n", [Activated-DateTime], [Resolved-DateTime])

                                                                          Data Type: Number

                                                                          Display format: (Blank)

                                                          This new column should give you the functionality expect from the OOTB calculated field.

                           

                                                          DEFECT WARNING: Date-time fields seem to have different formats, service analytics cannot do formulas very well in these cases.

                                                          DEFECT WORKAROUND: Create formula column for each time date field (no formula, just the field variable), with Data Type: DateTIme and Display Format: MM/dd/yyy hh:mm:ss. Use these new columns as the variables in the next step.

                                                   TimeDateMetircs.JPG

                                                       

                                                         

                          -Export Data to excel

                           

                          INTO EXCEL

                          (My preference; Remove all formatting and extra rows that footprints provides)

                          From here, it really depends on the actual metric you're trying to find. In this example, I'll source the Real Time of Activation-To-Resolution of each ticket and average over all of them.

                           

                          - Create a new Column for the normalized real time information.

                                          NOTE: In this example, The raw data in minutes will be in column K

                                          -In the first cell, add this formula to normalize the raw minutes data. K2 is the cell of the raw data in minutes.

                                          =INT(K2/1440)&" days "&INT(MOD(K2/1440,1)*24)&" hours "&INT(MOD(MOD(K2/1440,1)*24,1)*60)&" minutes"

                                          TimeDateMetircs2.JPG

                           

                          - Create a column to average the raw minutes:

                                          NOTE: in this example, this will be column O

                                          =AVERAGE(K:K)

                                       TimeDateMetircs3.JPG

                           

                          - Create overall average

                                          NOTE: This will use the last step's data as a variable.

                                          =INT(O2/1440)&" days "&INT(MOD(O2/1440,1)*24)&" hours "&INT(MOD(MOD(O2/1440,1)*24,1)*60)&" minutes"

                                         TimeDateMetircs4.JPG

                          1 of 1 people found this helpful
                          • 10. Re: Calculated Fields Need Definitions
                            Eric Liszt

                            Hi Crystal,

                             

                            Do you have a case number with all this info or better yet, has Support created a defect for you regarding this?   If I have those numbers, I can track down the situation for you. 

                             

                            Eric