3 Replies Latest reply: Oct 24, 2018 10:02 AM by Julien Le Berre RSS

    Measure at end of period

    Julien Le Berre



      I would like to calculate a measure at the end of a selected period (months), and one year ago this max month selected

      Ex: Select May/June/July 2018 -> 2 measures calculated:  one based on July 2018 period, the other one on July 2017


      Sum({<%date={"$(=Date(Max(%date)))"}>}%nb_subscriptions) is OK for the first measure


      Sum({<%date={"$(=AddMonths(Max(%date),-12))"}>}%nb_subscriptions) is KO for the second measure, if I select a month (returns 0)


      Could someone help me?



        • Re: Measure at end of period
          Niclas Anderström



          What if you add the Date() function to the second set-expression like you have in the first one? That is the only difference I can see that would cause an issue, other than if there are other selections made that influence the final result.

          • Re: Measure at end of period
            omar bensalem

            That's normal; because if u select a for example , JULY 2018, Qlik, with its associative engine will only SEE the data for JULY 2048.


            While with ur expression, u're willing to return :

            sum subscriptions for date = max(date)-12 (July 2017)


            it automatically returns 0 because qlik only sees data for july 2018.


            Now to be able to return back in time, u have to explicitaly tell Qlik to keep seing all the MONTHS, so that even when u select a month, it won't see only that month, but all months and only use the selected month to return the max(date) of the selected month. (hope that's rather clear)


            Sum({<Month, %date={"$(=AddMonths(Max(%date),-12))"}>}%nb_subscriptions)

            And for ur information, ur expression, will return only the data of the MAX date of the selected month, not ALL the MONTH

              • Re: Measure at end of period
                Julien Le Berre

                ok thanks!


                I thought that the "date=..." in the set analysis, would overwrite the current selection on the "date" field.


                But I've found a solution that seems ok:


                - in my script, I duplicate my measure like this:




                     "currentyear" as type,




                Resident temp;


                Concatenate (measures)


                     "12monthsago" as type,


                     AddMonths(date,12) as date

                Resident measures


                - in my sheet: