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.
based on your sample workbook, can you explain what's expected result by manually calculating?
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.
If you don't know how to calculate the correct result, how can I help you?