topic Re: Compare two months (Current vs Previous) in QlikView

Compare two months (Current vs Previous)

Wed, 10 Jul 2013 17:51:23 GMT

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

Re: Compare two months (Current vs Previous)

stevelord — Wed, 10 Jul 2013 19:02:49 GMT

(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.

Re: Compare two months (Current vs Previous)

jagan — Wed, 10 Jul 2013 20:06:20 GMT

Hi,

Please find attached file for solution.

Regards,

Jagan.

Re: Compare two months (Current vs Previous)

stevelord — Wed, 10 Jul 2013 20:08:33 GMT

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. )

Re: Compare two months (Current vs Previous)

jagan — Wed, 10 Jul 2013 20:16:21 GMT

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.

Re: Compare two months (Current vs Previous)

Wed, 10 Jul 2013 21:17:29 GMT

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?

Re: Compare two months (Current vs Previous)

jagan — Wed, 10 Jul 2013 21:36:03 GMT

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.