Comparative Columns with a Pivot Table

[julian_rodriguez]

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 !

[Anonymous]

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

[julian_rodriguez]

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!

[Anonymous]

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

[julian_rodriguez · Accepted solution]

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