topic Re: Set analysis and max value in QlikView

Set analysis and max value

Fri, 14 Nov 2014 11:30:29 GMT

Hi,

I am trying to use set analysis to show each month of current year against previous year (quite common).

My problem is when no year is selected : then, I want to choose the max year of my data model.

I want to use this expression to get the previous year:

=(max(Year) - 1)

Now, here is my problem: I have three years in my data (2010, 2011, and 2012).

I have a text expression that shows =(max(Year) - 1).

If 2011 is selected, it shows 2010. If 2012 is selected, 2011. If nothing is selected, 2011. So far so good.

Now I have a bar chart, with month as a dimension, and the following expression :

=Sum( {$<Month =, Year = {"=(max(Year)-1)"}>} Sales)

Now, if I select 2012, sales for each month of 2011 are shown. (and text expression shows 2011, as said).

BUT... if I select nothing, text expression still shows 2011, but the chart shows the sum of all Sales for each month !

I must be missing some fundamental point of Qlikview internals... so any help would be appreciated.

Franck

sushil353 — Fri, 14 Nov 2014 11:38:27 GMT

Hi,

Try below expression:

Sum({<Month=,Year={"=$(=Max(Year)-1)"}>}Sales)

HTH
sushil

Gysbert_Wassenaar — Fri, 14 Nov 2014 11:41:32 GMT

This should work: =Sum( {$<Month =, Year = {$(=max(Year)-1)}>} Sales)

Fri, 14 Nov 2014 13:11:37 GMT

Thanks you both, this works indeed.

Do you have any insight on what is happening, or any pointer to an in-depth explanation if this syntax ? This remains quite magical to me...