topic Re: Show on in Qlik Automate

Show on

Phara — Mon, 15 Aug 2022 20:27:09 GMT

Hello folks,

I'd like to know how can I display in a graphic the data of the 12 months prior without showing the current month. For an example, we are in August, but I just want to show the 12 months before that are completed, so it would be august 2021 to july 2022.

Thank you for your help!

Re: Show on

Digvijay_Singh — Mon, 15 Aug 2022 20:42:25 GMT

You can achieve this using various techniques but I prefer creating an indicator in the script like this -

if(monthend([Month Year]) >= monthend(addmonths(today(1),-12)) and monthend([Month Year]) < monthend(today(1)), 1,0) as _current_month_rolling13_ind

and then use this indicator in your measures like this -

Sum({<_current_month_rolling13_ind={1}>}Sales)

But if you need to respect the user selections of the month year field then the expression needs to be written in the set expression directly.

Thanks,

Re: Show on

Taoufiq_Zarra — Mon, 15 Aug 2022 20:52:48 GMT

Hello @Phara
several proposals,
for example if you want to apply the filter in dimension, you can use:

If(Date >= AddMonths(Today(), -11), Date)

or in measure

=SUM({$<DateField = {'>=$(=AddMonths(YearStart(Today()),-11))<=$(=AddMonths(MonthEnd (Today()),-12))'}>} YOURMEASURE)

Re: Show on

Phara — Mon, 15 Aug 2022 21:48:18 GMT

Hi Taoufiq,

I tried to add this formula If(Date >= AddMonths(Today(), -11), Date) in my dimension and it shows that there is an error. I just wanted to make sure that it's the right formula, nothing is missing.

Thanks

Re: Show on

Frank_S — Mon, 15 Aug 2022 22:35:25 GMT

You have an unneeded comma and a missing 'else' value in your formula

Invalid sample formula syntax

=If(Year < Year(now(), -2), Year)

Valid sample formula syntax

=If(Year < Year(now() -2), Year,'Not Valid Year')

take out extra comment and add the 'else' value

Kind regards...