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
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:
Maybe you could apply the following comparisons. Instead of MonthRank=CurrentMonthRank, you could use:
STR_RESOLVED = Text(Date(Today(), 'MMM YYYY'))
And replace MonthRank=CurrentMonthRank-1 with:
STR_RESOLVED = Text(Date(AddMonths(Today(), -1)
By the way, and assuming that STR_RESOLVED field contain text values. By the way have you tried to parse to date when declaring your variables in your previous tests?
