Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problem with set expressions

Dear fellow developers,

In a chart with a date dimension called GenDat, i'm trying to calculate the difference between this period's sales and the previous period's sales. I'm trying to achieve this using set analysis, but I can't get it working.

This expression works:

sum({<GenDat={'$(=AddMonths('1-3-2011',-1))'}>} Total Sales)

The period to which I'm comparing is fixed to 1-3-2011 for debugging purposes, 'Total' is necessary because I need to override the GenDat dimension value. But if I try to replace this fixed period by the variable GenDat, which seems a small step now, it stops working:

sum({<GenDat={'$(=AddMonths(GenDat,-1))'}>} Total Sales)

This expression results in only zero values. The expression AddMonths(GenDat,-1)) works well stand-alone if GenDat is restricted to a single value.

Why won't the second expression lead to the correct results, and how can I achieve what I'm trying to achieve?

Any help would be much appreciated!

Regards,

Sascha.

4 Replies
Not applicable
Author

Set analysis is calculated once per chart not once per row, which is why it works if you select one date but doesn't work when you have multiple dates.  You can fix this with either the aggr function, the firstsortedvalue function, or probably by changing your data model in some way.  Personally I would use aggr even if it is slow b/c I've never really used the firstsortedvalue function.

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Trent described why SA doesn't work - it can't be sensitive to your Dimension values. The common approach to this problem is to create another data table called "As Of Date" and link it to your transaction date. THe explanation of how to do it is fairly long, but there were numerous earlier threads on this forum describing the "As Of Date" logic. Look it up.

Not applicable
Author

As our fellow developers said that we can't use set analysis twice in a chart why can u give try with time charts in Qlikview but the problem is I think they are avail only in version 10 .

If u are using version 10 give a try Them .

Thank you

Not applicable
Author

I decided to use the Above() function, and it works ok now, although it's not my preferred method. I find it hard to believe that something this common is so hard realize in QV.