5 Replies Latest reply on May 15, 2018 9:48 PM by Zhouyi Zhang

    Tickets Mgm LOD

    vijay vag

      Hi All,

       

      I have created a dashboard for Tickets Management, like #tickets created, #Tickets reopened,#Tickets with Critical & High status and so on.

      Purpose is to measure the ticket counts over the duration for each metric.

       

      There are 3 terms to measure them with a duration filter:

       

      1. Actual-Actual distinct count of tickets

       

      2. Plan-"Plan" as a static average for the first 6 months,it will be the monthly average for the first 6 months of tracking.

      So if we had 200 tickets per month for the first 6 months then our monthly average will be 200.

      That same monthly average for a 6 month filter would be 1200 for the plan. Then the actual will be the actual numbers for the chosen time period.

      In this case, if we selected the filter to show the last month, then the plan would be 200 and the actual would be the actual number of tickets we had for the last month.

      Might be 20, might be 3000, it depends on the tickets for that month.

       

      Filters are Select View(monthly,weekly,quarterly,yearly) and Last N(number of days entered), depending on which the Actual and Plan will be shown.

       

      3. and the last one is Variance which will be Actual/Plan.

       

      I am unable to get the Plan correctly and it seems like there is something wrong with my LOD (probably static avg. for first 6 months ), if someone could point out and correct would be great.

      Due to this error I am getting 600% variance for all my metrics.

       

      I have attached my workbook for your reference. Thank you!

        • 1. Re: Tickets Mgm LOD
          Zhouyi Zhang

          Hi, Vijay

           

          Is it something like this? Not sure what's your expected result.

           

           

          Hope this helps

           

          ZZ

          • 2. Re: Tickets Mgm LOD
            vijay vag

            Thank you for your input Zhouyi Zhang

             

            But I am mainly looking to construct the below part for "Plan", which looks dynamic in your screenshot.

             

            Plan-"Plan" as a static average for the first 6 months,it will be the monthly average for the first 6 months of tracking.

            So if we had 200 tickets per month for the first 6 months then our monthly average will be 200.

            That same monthly average for a 6 month filter would be 1200 for the plan. Then the actual will be the actual numbers for the chosen time period.

            In this case, if we selected the filter to show the last month, then the plan would be 200 and the actual would be the actual number of tickets we had for the last month.

            Might be 20, might be 3000, it depends on the tickets for that month.

             

            If you see in the screenshot attached by you, Actual Val/Plan Val approximately is always giving you %Age as 600% and that is the main problem with my dashboard because the Plan has not been calculated properly.

            • 3. Re: Tickets Mgm LOD
              Zhouyi Zhang

              Hi, Vijay

               

              based on your sample workbook, can you explain what's expected result by manually calculating?

               

              ZZ

              • 4. Re: Tickets Mgm LOD
                vijay vag

                Hi Zhouyi,

                 

                That is where i am having a hard otherwise i could have shown you. But I had drafted a set of questions you might have that will help you calculate this Plan Value.

                 

                1. By first 6 months, do you mean the first 6 months of data that you have? (i.e. from the sample data in the workbook this would be from July 2014), or do you mean the latest 6 months?

                It should be the latest 6 months(from Dec 2017-May 2018) for now, if last 6 months have been selected in the filter and this will always dynamically select the latest 6 months.

                 

                2.If it is the first 6 months, is this always the same 6 months, or will this vary by Category/Type?

                 

                Yes, it is the first and the latest 6 months. It won’t always be the same 6 months, it will dynamically be the latest 6 months, and will vary by Category/Type.

                 

                3. Related to the above, how do we handle NULLs? i.e in the below image for "# Ticket type adjusted from Incident to Service request" there is only one value (October 2014), so should the monthly average here be 2, or 2/6 = 0.33  (whether the answer to Question 1 is first, or last 6 months I still need to know how to handle NULLs)

                 

                That’s correct-0.33, but we are considering the first 6 months, in this case it would be from Dec 2017-May 2018 . Also, nulls should be treated as 0.

                 

                4. how does the plan change, if someone selects "week" (say) from the parameter? Do we now use the first 6 week average? or do we still use the first 6 months, but scale this to be a weekly average?

                 

                We should use the first 6 months data but scaled to the selected filter. Same for days/quarters/year.

                • 5. Re: Tickets Mgm LOD
                  Zhouyi Zhang

                  Hi, Vijay

                   

                  If you don't know how to calculate the correct result, how can I help you?

                   

                  ZZ