Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Zara
Contributor II
Contributor II

Set analysis - limit measure by dimension

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….

 

Zara_0-1606137396782.png

 

 

And I want it to look like this…..

Zara_1-1606137396786.png

 

 

 

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.

1 Solution

Accepted Solutions
Taoufiq_Zarra
Master II
Master II

@Zara 

Maye be like this ?

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

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

1 Reply
Taoufiq_Zarra
Master II
Master II

@Zara 

Maye be like this ?

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

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post