Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
LP27
Creator II
Creator II

Rolling 12 months for sum and count

Hi everyone,

I want to have rolling 12 months on Sum and Count measure.

I have seen many post related to this, but nothing worked well with my scenario-

here is my scenario -

I have field as =

This is my Datefield =Period("MMM-YYYY") 

Sum(sales)

Count({<Sale= {'External'} >} ExternalSale)

So now i need to have rolling 12 months(From this October 2019 to October 2018)  in the above sum and Count measure.

what is the best way ?

 

Thanks in advance! 

 

 

 

 

 

Labels (4)
1 Solution

Accepted Solutions
LP27
Creator II
Creator II
Author

Finally with some trail and error method i came up with right fix.

Please find the script below-

SUM({< Period = {">=$(=Date(MonthStart(Today(),-12),'MMM-YYYY'))<=$(=Date(MonthStart(Today()),'MMM-YYYY'))"} >}[Sales})

Count({<Sale= {'External'}, Period = {">=$(=Date(MonthStart(Today(),-12),'MMM-YYYY'))<=$(=Date(MonthStart(Today()),'MMM-YYYY'))"} >} ExternalSale)

 

Thanks to myself! 🙂

 

View solution in original post

1 Reply
LP27
Creator II
Creator II
Author

Finally with some trail and error method i came up with right fix.

Please find the script below-

SUM({< Period = {">=$(=Date(MonthStart(Today(),-12),'MMM-YYYY'))<=$(=Date(MonthStart(Today()),'MMM-YYYY'))"} >}[Sales})

Count({<Sale= {'External'}, Period = {">=$(=Date(MonthStart(Today(),-12),'MMM-YYYY'))<=$(=Date(MonthStart(Today()),'MMM-YYYY'))"} >} ExternalSale)

 

Thanks to myself! 🙂