Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
marcus_steggall
Creator
Creator

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
Anil_Babu_Samineni

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
Contributor III
Contributor III

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

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)
marcus_steggall
Creator
Creator
Author

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

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)