Variations of calculations, using set analysis

[marcus_steggall]

Afternoon all,

Hopefully this is possible, within some sort of calculation using variables, but I haven't had much looking getting it to work.

I have a dashboard, working on vMaxYear etc, so when you select 2017 (current year), it will also show another column for the previous year 2016.

However, is it possible to have a rolling 12 months calculation as another column?

Say you select November 2017, you would want to see 3 columns;

1. Current Year = Jan to Nov 2017
2. Previous Year = Jan to Nov 2016
3. Rolling 12 months = Dec 2016 to Nov 2017

Doesn't seem to like that combination, so this makes me feel like it's not possible

But surely not, this is qlikview the powerful tool.

Any help would be amazing and much appreciated.

Many thanks and Merry Christmas, hope it's a fanastic time with family and a well deserved break.

[venelin_rs]

Hi Marcus,

Try with this:

Sum({<Year=, Month=, Quarter=, Week=, Date_ID={">=$(=num(AddMonths(MonthStart(Date(Max(Date_ID))), -13)))<=$(=num(AddMonths(Date(Max(Date_ID)), -1)))"}>} Sales)

Note: You can play with the number of months.

[Anil_Babu_Samineni · Accepted solution]

Yes, Perhaps this?

Current Year

Sum({<MonthYear = {">=$(=Date(YearStart(Max(MonthYear)),'MMM YYYY')) <=$(=Date(Max(MonthYear),'MMM YYYY'))"}>} Sales)

Previous Year

Sum({<MonthYear = {">=$(=Date(AddYears(YearStart(Max(MonthYear)),-1),'MMM YYYY')) <=$(=Date(AddYears(Max(MonthYear),-1),'MMM YYYY'))"}>} Sales)

Rolling 12 Months

Sum({<MonthYear = {">=$(=Date(AddYears(Max(MonthYear),-1),'MMM YYYY')) <=$(=Date(Max(MonthYear),'MMM YYYY'))"}>} Sales)

[marcus_steggall]

Hi Anil,

Many thanks for coming back to me so quick

Hmm I can't seem to get this working, what's your format of MonthYear and Date?

Many thanks

[Anil_Babu_Samineni]

Mine formats are MMM YYYY and DD.MM.YYYY