3 Replies Latest reply: Sep 20, 2017 9:49 AM by daniel melo RSS

    Variables in set analysis with Qlik Sense

    daniel melo



      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:


        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