5 Replies Latest reply: Aug 12, 2016 4:24 PM by Pablo del Cerro RSS

    Dynamic Filter values

    Govind Ramchetty

      Hi

      I am using Qlik sense.

      If i pass variable values then i can able to see correct values.

       

      I defined VpreviouseYear=2015 in load manager.

       

      Sum({$<[ACT_END_DATE.autoCalendar.Year] = {$(VPreviouseYear)}>} [Grand Total])

       

      But this is hardcoded.

       

      My requirement is When i select my filter pane (ACT_END_DATE.autoCalendar.Year) 2015 then my Grand Total should be 2014 sum of grand total..

       

      How can i achieve this.

       

      Thanks

      Govind R

        • Re: Dynamic Filter values
          kvp kumar

          Hi Govind,

           

          try this,

          Sum({$1<[ACT_END_DATE.autoCalendar.Year] = {$(VPreviouseYear)}>} [Grand Total])


          Thanks

          Kumar

          • Re: Dynamic Filter values
            Sunny Talwar

            May be try this:

             

            Sum({$<[ACT_END_DATE.autoCalendar.Year] = {$(=Max([ACT_END_DATE.autoCalendar.Year]) - 1)}>} [Grand Total])

            • Re: Dynamic Filter values
              Pablo del Cerro

              Hi there,

               

              Sunny's solution perhaps is not working because the Autogenerate Year field is a dual with Text and Date. If you see the definition it states:

              Dual(Year($1), YearStart($1)) AS [Year]

               

              So, if you MAX and -1, it will bring you last day of previous year.

               

              You need to work with formulas like Year() if you want to have the set analysis in TEXT, o YearStart() if you want to use the set analysis with date (the num part of the dual).

               

              Not sure if Qlik will "fix" this dual field, so be carefull for what changes may happen in the future.

               

              So, i guess something like this should work on your variable:

              =Year(Max([FECHA.autoCalendar.Year]))-1

              or

              =YearStart(Max([FECHA.autoCalendar.Year])-1)

               

              The first example on the SetAnalysis you will need to say '$(VpreviouseYear)' because it will be text. On the second one it will be a date.

               

              I hope this helps.

               

              Pablo