Rolling 12 months sales

[profilejamesbond]

Hi,

I made this expression, the problem is whenever I am selecting year, month, quarter then the bar chart only showing the selected months or year data. For example, I select 2025 then it shows me Jan, Feb, Mar data while it has to show last 12 Months of data based on selection and given below expression in Qlik Sense:

Sum({$<type= {'A'}, date = {">=$(=Addmonths(Monthstart(Max(date)),-11))<=$(=Max(date))"}>} sales)

How to make it like rolling 12 months?

Thanks

[profilejamesbond]

Experts any help...

[Chanty4u · Accepted solution]

Try this 

Sum({$<type= {'A'}, date = {">=$(=AddMonths(Today(),-11))<=$(=Today())"}, Year=, Month=, Quarter=>} sales)

[profilejamesbond]

Hi @Chanty4u,

Looks perfect, but don't understand the idea. Why ignoring Year, Month, Quarter in expression.

Please describe, little-bit more.

Thanks

[AJDoc31]

What I do is do it by the MonthsAgo formula

In the load script i have 

12*Year(Today())+Month(Today())-12*Year([Date])-Month([Date]) AS MonthsAgo

Then I create two variables (vRolling12MonthMax and vRolling12MonthMin) with formulas:

vRolling12MonthMax

=IF(ISNULL(GetFieldSelections(Year))

,12

,12*Year(Today())+Month(Today())-12*Year(Max(Date))-Month(Max(Date)))

vRolling12MonthMin

=IF(ISNULL(GetFieldSelections(Year))

,0

,12*Year(Today())+Month(Today())-12*Year(Min(Date))-Month(Min(Date)))

Finally, for set analysis I have

Sum({<[MonthsAgo]={">=$(=vRolling12MonthMax)<=$(=vRolling12MonthMin)"}>}
Sales)

If there are other time filters you would have to adjust the if statement for the variables for those additional selection fields. Hope this helps!

[AJDoc31]

I think for this solution you would want to do max date not Today()

so like 

Sum({$<type= {'A'}, date = {">=$(=AddMonths(Max([Date]),-11))<=$(=Max([Date]))"}, Year=, Month=, Quarter=>} sales)

Ignoring the year, month, and quarter will allow dates to show that cross these boundaries (i.e. showing December 2024 if year 2025 is selected). I could be wrong though!