Hello Everyone,
I have been reading through numerous posts and calculations throughout the forums in an effort to calculate month over month changes for my data. Unfortunately as I try various proposed solutions I can't seem to get any to work with my data in my situation. So apologies if any of this is redundant, but would really appreciate some assistance as I have taken this dashboard over from someone else and their methodology.
I have a field called STR_RESOLVED. The data in there is the Month and Year as follows:
May 2016
April 2016
March 2016
... all the way back to May 2013
I have Expressions that I need to measure as follows:
VOLUME
CASES
ONE DAY RESOLUTION
AVERAGE DAYS TO RESOLUTION
and more...
Each of these expressions has a definition with variables using MonthRank and CurrentMonthRank, for example
AVERAGE DAYS TO RESOLUTION shows this expression:
=num(((Sum(if(MonthRank=CurrentMonthRank,[Case Age],0))/Count(if(MonthRank=CurrentMonthRank,[Case Age])))-
(Sum(if(MonthRank=CurrentMonthRank-1,[Case Age],0))/Count(if(MonthRank=CurrentMonthRank-1,[Case Age]))))/
(Sum(if(MonthRank=CurrentMonthRank-1,[Case Age],0))/Count(if(MonthRank=CurrentMonthRank-1,[Case Age]))),'+0.00%')
The problem is, the prior owner:
Created a field in Access, script loaded it from the DB and called it MonthRank assigning each month a value, like 43,44,45 through 54.
Created a variable called CurrentMonthRank, which I cannot see how it is updated, but currently has a value of just 54.
Since I no longer have these two items, nothing calculates for the above expression I need to measure.
I believe the logic I am looking for is how to create a replacement value for MonthRank and CurrentMonthRank that I could populate in those which would update all my existing expressions.
I've been trying everything I can think of from , ( vMaxMonth =month(max(STR_RESOLVED)), and vPriorMonth =month(addmonths(max(STR_RESOLVED) ,-1)) to using Accountnum for STR_RESOLVED, as well as various SET or LET values and probably due to my limited knowledge (this is my first dashboard), cannot get this to work. I've seen mention of a Calendar Table, but I don't seem to have one of those either.
In a nutshell, can someone please direct me on how I can replicate/replace or alternatively achieve the MonthRank and CurrentMonthRank comparisons so that I can present month over month %change for each expression like as follows:
| Expressions | April 2016 | March 2016 | MoM% |
|---|
| VOLUME | 120,935 | 140,776 | -14.09% |
| CASES | 44,503 | 51,675 | -13.88% |
| ONE DAY RESOLUTION | 58% | 57% | 0.06% |
Thank you very kindly!
