Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

danimelo1
New Contributor III

Variables in set analysis with Qlik Sense

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)}>}[VolumeSmiley TongueROD, 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

Tags (2)
1 Solution

Accepted Solutions

Re: Variables in set analysis with Qlik Sense

Maybe add in a dollar expansion :


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

3 Replies

Re: Variables in set analysis with Qlik Sense

Maybe add in a dollar expansion :


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

agigliotti
Honored Contributor II

Re: Variables in set analysis with Qlik Sense

try using:

vCurrentYear= Max(Year)

vPastYear= (Max(Year)-1)

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

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

danimelo1
New Contributor III

Re: Variables in set analysis with Qlik Sense

Thanks Andrea!! This works too!

Community Browser