Set analysis - limit measure by dimension

[Zara]

Hey guys,

I’ve been working on a report for days and I am pulling my hair out, is there any chance anyone can help me please. I’m not sure what i want to do is even possible……

I have a dimension called ‘monthend’ and a measure sum of sales.

Its worth noting that Qlik does not consider ‘monthend’ as a date field due to its format of ‘202010, 202011’ however in my eyes I would know this is October and November.

I am looking to produce a line graph but I want the line to stop 5 months before the end, I want it to be rolling but I want the dimension to continue.

So in its very basic form, and  as an example, it currently looks like this….

And I want it to look like this…..

So essentially I want the formula to say look at the latest dimension (202011 in this example) count back 5 and then sum sales.

I can do sum({<[MonthEnd = {‘<=202006’}]>}Sales) but this would mean I would need to back into the report every month and change the formula, I wanted one that was rolling if possible.

Make sense? Thanks to anyone in advance.

[Taoufiq_Zarra · Accepted solution]

@Zara 

Maye be like this ?

=sum({<MonthEnd = {"<=$(=Date(Addmonths(Max(Date#(MonthEnd,'YYYYMM')),-5),'YYYYMM'))"} >} Sales)