topic Comparative Columns with a Pivot Table in QlikView

Comparative Columns with a Pivot Table

julian_rodriguez — Wed, 23 May 2012 22:34:07 GMT

Hello there, I'm new with QV and Set Analysis

I have a Pivot table with months (YearMonth) as Dimension and one Count (Files) as Expression. I need a new colum (expression) with the comparative value between the month and the last month,

I'm using set analisys but I don't know what the problem is... this is the code for the second column:

COUNT({<YearMonth={"MONTH(AddMonths(YearMonth, -1))"}>} Files)

What is the problem with that?...

Thanks for your help !

Comparative Columns with a Pivot Table

Anonymous — Thu, 24 May 2012 10:32:33 GMT

Julian,

The first problem is the syntax:
COUNT({<YearMonth={"$(=MONTH(AddMonths(YearMonth, -1)))"}>} Files)
The second problem is the data format. Make sure that expression
=MONTH(AddMonths(YearMonth, -1))
returns data in the same format as YearMonth field. I suspect that YearMonth includes both month and year, while the expression returns only month. Maybe this is closer to what you need, assuming YearMonth format is YYYY-MMM:
=date(AddMonths(YearMonth, -1),'YYYY-MMM')

(I'm not sure if these are the only problems, just a quick look...)

Regards,
Michael

Re: Comparative Columns with a Pivot Table

julian_rodriguez — Thu, 24 May 2012 13:34:20 GMT

Hello Michael, thanks for your suggestion. I have changed the code in order to be sure that the format is equal, so this is it:

=COUNT({<YearMonth={"$(=MONTHNAME(AddMonths(YearMonth, -1)))"}>} Files)

Now, the operation always result in zero (0). Here is a screenshot of the table:

Dimensions: Zones and months

Expressions:

A . Count of Files of the month

B . Percentage of Files on the month

C . Variation against last month (my problem)

Thanks for your help!

Comparative Columns with a Pivot Table

Anonymous — Fri, 25 May 2012 10:17:45 GMT

Julian,

For testing, try the next expressions:
= YearMonth
=MONTHNAME(AddMonths(YearMonth, -1))
I suspect they are still different, for example the first expression returns 'feb 2012' while the second - 'Feb 2012'

Regards,
Michael

Re: Comparative Columns with a Pivot Table

julian_rodriguez — Fri, 25 May 2012 13:10:56 GMT

Hello Michael and everyone...

I have solved my problem with the help of a friend of mine.

He suggested me to use the BEFORE function. This was the code I use for the expression:

Before((COUNT(Distinct Files)))/COUNT(Distinct Files)

And this is it.

Thanks for your time and help!

Regards,

Julian