Month over Month Calculation for Expressions

Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Month over Month Calculation for Expressions

Anonymous — Sat, 28 May 2016 02:41:42 GMT

Hi Christopher,

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?

vMaxMonth = month(max(Date#(STR_RESOLVED, 'MMM YYYY')))

vPriorMonth = month(addmonths(max(Date#(STR_RESOLVED, 'MMM YYYY')) ,-1))

Best Regards,

-- Karla

Re: Month over Month Calculation for Expressions

sunny_talwar — Sun, 29 May 2016 03:37:43 GMT

Would it be possible to provide some sample data with the expected output? It might get easy to help you with those things.

QlikCommunity Tip: How to get answers to your post?

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

topic Re: Month over Month Calculation for Expressions in QlikView

Month over Month Calculation for Expressions

Re: Month over Month Calculation for Expressions

Re: Month over Month Calculation for Expressions