Compare two months (Current vs Previous)

[#1]

Hi Community,

I have a dataset which contains 'ratings' of customers for all 12 months (see example below). I also have a calendar object to select current month in my dashboard. Whenever a user selects a 'current date', I intend to show a difference of 'ratings' between for the month selected ('current month') and month prior to it ('previous month').

CustName,     Month,     Rating

A,                    05/31/2013,     4

A,                    06/30/2013,     5

B,                    05/31/2013,     7

B,                    06/30/2013,     3

C,                    05/31/2013,     9

C,                    06/30/2013,     7

The result should be (when user selected 06/30/2013 as current date):

CustName,     Rating Change

A                         1                   

B                         -4

C                         -2

Please advise. Thanks

[stevelord]

(Update: corrected some mistyped parts)

This one is stumping me in Qlikview as well, but I have a pretty good workaround.

First in script you can Month(Date) as Month to pull out the Months into a field.

Then on a pivot table you have Customer and Month for Dimensions and SUM(Aggr(Rating, Max(Date), CustName)) for an expression (Label the expression 'Rating')

Then on the pivot table, dragthe Month field over from the left Y-axis to the top X axis.

You should then see something like

Month               May     June

CustName

A                         4          5

B                         7          3

C                         9          7

That's as far as I got in Qlikview. After that, I'd export to Excel add a column and write =C3-B3 and copy that down to get the changes by customer. 

Feel free to award helpful if this helped you get the job done for now.  I know it's not the complete/correct answer.

[jagan]

Hi,

Please find attached file for solution.

Regards,

Jagan.

[stevelord]

Can you paste the code into the body of the post?  I'm on personal edition and can't open the attachment.  (I'm not the original poster but had the same obstacle they had. )

[jagan · Accepted solution]

Hi Steve,

This is the expression i used in the expression

=Sum(Rating) - Sum({<Month={"$(=Date(MonthEnd(Max(Month), -1), 'MM/dd/yyyy'))"}>}Rating)

Sum(Rating) - Will give you the selected month value

Sum({<Month={"$(=Date(MonthEnd(Max(Month), -1), 'MM/dd/yyyy'))"}>}Rating) - Will give you the previous month value.

Regards,

Jagan.

[#6]

Thanks Jagan.. it worked wonders!

Is there a way to get a count of all the +ve changes and -ve changes using the same expression?

[jagan]

Hi,

Try like this

For Positive values:

=Sum(If(Sum(Rating) - Sum({<Month={"$(=Date(MonthEnd(Max(Month), -1), 'MM/dd/yyyy'))"}>}Rating) > 0,1))

For Negative values:

=Sum(If(Sum(Rating) - Sum({<Month={"$(=Date(MonthEnd(Max(Month), -1), 'MM/dd/yyyy'))"}>}Rating) < 0,1))

Hope this helps you.

Regards,

Jagan.