topic Re: Variations of calculations, using set analysis in QlikView

Variations of calculations, using set analysis

marcus_steggall — Wed, 25 Nov 2020 16:16:04 GMT

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;

Current Year = Jan to Nov 2017
Previous Year = Jan to Nov 2016
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.

Re: Variations of calculations, using set analysis

venelin_rs — Mon, 18 Dec 2017 15:35:00 GMT

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.

Re: Variations of calculations, using set analysis

Anil_Babu_Samineni — Mon, 18 Dec 2017 15:40:06 GMT

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)

Re: Variations of calculations, using set analysis

marcus_steggall — Tue, 19 Dec 2017 15:45:20 GMT

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

Re: Variations of calculations, using set analysis

Anil_Babu_Samineni — Tue, 19 Dec 2017 16:14:37 GMT

Mine formats are MMM YYYY and DD.MM.YYYY