Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
How about this ?
=Sum({<Date={"$(='>=' & Date(MonthStart(Max(Date), -12)) & '<=' & Date(MonthStart(Max(Date))))"}>} Sales)
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.
same problem as well... I can't change the model, so I need to do set analysis.
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.
Not working. But I'm not able to share the company data..sorry
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)
May be try this:
Sum({<Date={">=$(=ADDMONTHS(MAX(Date), -12))<=$(=Date(MAX(Date)))"},Year=,Month=,Quarter=>}Sales)
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)
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?