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

    Measure at end of period

    Julien Le Berre

      Hello

       

      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?

       

      Thanks!

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

          Hi,

           

          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:

                 

                measures:

                Load

                     "currentyear" as type,

                     nb_subscriptions,

                     date,

                     ...

                Resident temp;

                 

                Concatenate (measures)

                Load

                     "12monthsago" as type,

                     nb_subscriptions,

                     AddMonths(date,12) as date

                Resident measures

                 

                - in my sheet:

                Sum({<type={'currentyear'},%date={"$(=Date(Max(%date)))"}>}%nb_subscriptions)

                Sum({<type={'12monthsago'},%date={"$(=Date(Max(%date)))"}>}%nb_subscriptions)