2 Replies Latest reply: Feb 15, 2016 7:42 AM by Quyen Tran RSS

    [QlikSense]Calculation following filter?

    Quyen Tran

      Hello,

      I want to compare the revenue with that of the previous year following the month and the year that we filter.

       

      If the filter is not applied, the month and the year is set following current date (below is the formula that we generated)

      ((sum({<month_n = {"<=$(vM)"}, year = {"$(vY)"}>}[revenue])

      - sum({<month_n = {"<=$(vM)"}, year = {"$(vY)"}>}[salary_euro])

      )

      /

      (sum({<month_n = {"<=$(vM)"}, year = {"$(vY1)"}>} [revenue])

      -sum({<month_n = {"<=$(vM)"}, year = {"$(vY1)"}>} [salary_euro])

      ))

      - 1

      Knowing that vM, vY and vY1 are defined in the script already. They are set to collect the current month, the current year and the previous year.

      Else the revenue will be sum up according to the month and the year chosen ( month and year can be multiple selection). -> I'm stuck here.

       

      How could I integrate the ELSE part in the formula above and run it in qliksense?

       

      THANK YOU FOR YOUR SUPPORT A LOT!

        • Re: [QlikSense]Calculation following filter?
          bruno bertels

          Hi

           

          Are your variable something like that ? :

          vM = month(today())

          vY = year(today())

          VY1 = year(today()-1)

           

          If it's that, may be when you select specific month in your filter pan your mesure still send back curent month rather than selected month ?

           

          may be try like this :

           

          vM=max(month_n)

          vY=max(year)

          vY1=max(year)-1

           

          so that without selection your mesure and set analysis will take as month the last month of the max year in your data

           

          if you select one or several month , your mesure will take as month the max month of your selected month and same with vY1 that will send back the previous year of the selected year or with no selection the previous year of your max year presents in your database.

           

          Hope it's help

           

          Bruno