Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Maybe add in a dollar expansion :
Sum({<Year={"$(=Max(Year)-1)"}>}[JTI Volume: RMC, MMU])
Maybe add in a dollar expansion :
Sum({<Year={"$(=Max(Year)-1)"}>}[JTI Volume: RMC, MMU])
try using:
vCurrentYear= Max(Year)
vPastYear= (Max(Year)-1)
Sum({<Year={"$(=$(vCurrentYear))"}>} [Volume: Prod, MMU])
Sum({<Year={"$(=$(vPastYear))"}>} [JTI Volume: RMC, MMU])
Thanks Andrea!! This works too!