Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
julian_rodrigue
Valued Contributor

Comparative Columns with a Pivot Table

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 !

1 Solution

Accepted Solutions
julian_rodrigue
Valued Contributor

Re: Comparative Columns with a Pivot Table

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

4 Replies
mov
Esteemed Contributor III

Comparative Columns with a Pivot Table

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_rodrigue
Valued Contributor

Re: Comparative Columns with a Pivot Table

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!

Tabla.jpg


mov
Esteemed Contributor III

Comparative Columns with a Pivot Table

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_rodrigue
Valued Contributor

Re: Comparative Columns with a Pivot Table

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

Community Browser