Dynamic Rolling Month requirement in Qlik Sense

[QlikBhavesh]

Hello Friends,

Hope you are doing well, I am new to Qlik Sense and need help in calculating rolling month based on Dynamic year selection.

Below is the formula which i am using to calculate 3 month rolling sum.

Rolling 3 Months=above(rangesum(below(sum({ [##_DISPATCH_QTY]),0,3)),2)

Rolling 6 Months =above(rangesum(below(sum({ [##_DISPATCH_QTY]),0,6)),5)

This formula works fine if there is no selection on Year column.

Once i select the year- If the data is available for 2017,2018 and User selects 2018 from Year filter then Rolling 3 months for Jan-2018 should be Nov17+Dec17+Jan18. With above formula its showing Jan18 and Feb18 as blank (Explained in attached excel). Similar issue for rolling 6 and 12 months as well.

I have attached sample data with example.

Appreciate your help.

Thank you,

Bhavesh

[y_grynechko]

Hey,

this is one of the ways to get the dynamic period of time in set analysis:

Sum({< [Invoice Date] = {"=[Invoice Date]>=AddMonths(MonthStart(Max(total [Invoice Date])),-2) and [Invoice Date]<=MonthEnd(Max(total [Invoice Date]))"}>} [Actual GL Amount])