Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
jleberre
Contributor II
Contributor II

Measure at end of period

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!

3 Replies
niclaz79
Partner
Partner

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
Partner
Partner

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
Contributor II
Contributor II
Author

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)