Variables in set analysis with Qlik Sense

[danimelo1]

all,

I have a problem with this. I have tried everything related to all post, specially this Using a variable in set analysis in Qlik Sense but it doesn't work.

I have the Year field:

Nivel1_1:

  LOAD SubField(FileName(), '_', 2) AS Year,

       SubField(FileName(), '_', 1) AS Region,   

      [F3] as Indicator,

      [F4] as Value

   FROM [lib://Path/*.xlsx]

  (ooxml, embedded labels, header is 1 lines, table is [Level 1_1])

  where not IsNull([F3]);

Then I create two variables:

vCurrentYear= Max(Year)

vPastYear= Max(Year)-1

Now I'm using it in a set analysis as follows, but it doesn't work neither for Current nor PastYear variable:

Sum({<Year={$(=vCurrentYear)}>}[Volume:PROD, MMU]), The result is a dash(-)

Then I tryed to do this directly as follows, but it doesn't work when I substract 1 from Max() function:

Sum({<Year={"=Max(Year)"}>}[Volume: Prod, MMU]) --->Works OK

Sum({<Year={"=Max(Year)-1"}>}[Volume: RMC, MMU]) ----> The same result as above.

I have tried to convert to numbers the variables but still doesn't work.

Any idea what I'm doing wrong? Maybe is there a problem with type of data?

Thank you

[Anonymous · Accepted solution]

Maybe add in a dollar expansion :

Sum({<Year={"$(=Max(Year)-1)"}>}[JTI Volume: RMC, MMU])

[agigliotti]

try using:

vCurrentYear= Max(Year)

vPastYear= (Max(Year)-1)

Sum({<Year={"$(=$(vCurrentYear))"}>} [Volume: Prod, MMU])

Sum({<Year={"$(=$(vPastYear))"}>} [JTI Volume: RMC, MMU])

[danimelo1]

Thanks Andrea!! This works too!