Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

ABOVE function in Set Analysis

Hi All,

I've been struggling to do a 12 month rolling sum in my bar chart, depending on the YearMonth selected by the user.

This needs to be a single value, based upon the YearMonth selected (the Chart is only shown if one YearMonth is selected, so that solves the initial problem of getting the user to select just one date)

In essence, these are the formulae I've been trying to work with:

Sum({1<YearMonth={$(=above(YearMonth,0,11))}>} [Sales]

---> Result is null...

RangeSum(above(sum({1<YearMonth=>} [Sales]),0,11)) 

---> Result is shown if I use YearMonth as a Dimension (as if I were accumulating 12 steps back), but I don't want to show the range, only one value for the 12 months prior to my selection

I've been experimenting with ABOVE, BELOW and RANGESUM, but nothing works as I need it to so far.

In my data, YearMonth is a text entry in the format YYYY/MM, so it's not as simple as YearMonth + YearMonth-1, etc...

I also considered concat() for Year and Month, but seeing as Month 1 = Month 01 (so if(len(Month)=1,'0'&Month)....),, it creates a lot more problems that just an ABOVE function.

I'm not using any Dimensions, as there are a number of expressions that use different time periods in this chart, and I don't want to screw these up.

Any ideas?

2 Replies
danielrozental
Master II
Master II

Above and set analysis don't really make any sense together.

Try building a sample and attaching it so you can get more help.

Not applicable
Author

In the end, I'm inclined to agree that using these 2 together just isn't practical...

Instead, the solution I've used is to add a field that adds a counter to the YearMonth - so if I want a 12 month rolling sum, I use set analysis for this month minus 12.

Hope this helps anyone else struggling with rolling 12 month analysis!