Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !
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
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
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!
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
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