Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: 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! 🙂