rolling 12 months doesn't work

[wanyunyang]

I'm using:

Sum({<Date={">=$(=ADDMONTHS(MAX(Date), -12))<=$(=MAX(Date))"},Year=,Month=,Quarter=>}Sales)

to calculate rolling 12 months sales (rolling from the max date within a selected time range). For example, if I select 09/20/2018-09/24/2018, I want the expression sum up sales from 09/24/2017-09/24/2018.

However, in current expression, if I select 09/20/2018-09/24/2018, it will show me sales for 09/20/2018-09/24/2018. I don't know how I can fix this expression... Please help!!

Any advice helps! Thanks in advance!!

[jayanttibhe]

How about this ?

=Sum({<Date={"$(='>=' & Date(MonthStart(Max(Date), -12)) & '<=' & Date(MonthStart(Max(Date))))"}>} Sales)

[jayanttibhe]

Also, Instead of using Complicated Set Analysis why dont you use 'As Of Table' for rolling totals.  s

See HIC's Post mentioned below.

https://community.qlik.com/blogs/qlikviewdesignblog/2015/11/02/the-as-of-table

OR

It is also a part of Rob's Qlikview Components Library.

https://github.com/RobWunderlich/Qlikview-Components

[wanyunyang]

same problem as well... I can't change the model, so I need to do set analysis.

[jayanttibhe]

Above expression Is  working or not ?  If not - you need to post sample app. If your model has fundamental issue then we need to look it at first.

[wanyunyang]

Not working. But I'm not able to share the company data..sorry

[Anil_Babu_Samineni]

Your expression, Seems fine to me. Could be Date format issue?

Sum({<Date={">=$(=Date(ADDMONTHS(MAX(Date), -12),'Your Format Here'))<=$(=Date(MAX(Date),'Your Format Here'))"},Year=,Month=,Quarter=>}Sales)

[balabhaskarqlik]

May be try this:

Sum({<Date={">=$(=ADDMONTHS(MAX(Date), -12))<=$(=Date(MAX(Date)))"},Year=,Month=,Quarter=>}Sales)

[balabhaskarqlik]

OR this:

Sum({<Date={‘>=$(=MonthStart(Max(Date), -11))<=$(=Date(Max(Date)))’},Year=, Quarter=, Month=>} Sales)

Sum({<Date={‘>=$(=Date(MonthStart(Max(Date), -11)))<=$(=Date(Max(Date)))’},Year=, Quarter=, Month=>} Sales)

[wanyunyang]

Hi Anil,

No it doesn't work...

I have Year, Month and Quarter filters in the layout, but in my master calendar I also have Week, Period, etc. Do I need to put Week=, Period= in the set analysis as well?