Measure at end of period

[jleberre]

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!

[niclaz79]

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.

[OmarBenSalem]

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

[jleberre]

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)