Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Maye be like this ?
=sum({<MonthEnd = {"<=$(=Date(Addmonths(Max(Date#(MonthEnd,'YYYYMM')),-5),'YYYYMM'))"} >} Sales)
Maye be like this ?
=sum({<MonthEnd = {"<=$(=Date(Addmonths(Max(Date#(MonthEnd,'YYYYMM')),-5),'YYYYMM'))"} >} Sales)