Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
marcus_steggall
Contributor

Variations of calculations, using set analysis

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.

1 Solution

Accepted Solutions

Re: Variations of calculations, using set analysis

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)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

4 Replies
venelin_rs
New Contributor III

Re: Variations of calculations, using set analysis

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

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)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

marcus_steggall
Contributor

Re: Variations of calculations, using set analysis

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

Mine formats are MMM YYYY and DD.MM.YYYY

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)