ABOVE function in Set Analysis

Thu, 26 Apr 2012 09:42:30 GMT

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?

ABOVE function in Set Analysis

danielrozental — Sun, 29 Apr 2012 23:54:42 GMT

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

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

ABOVE function in Set Analysis

Thu, 03 May 2012 14:29:18 GMT

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!

topic ABOVE function in Set Analysis in QlikView

ABOVE function in Set Analysis

ABOVE function in Set Analysis

ABOVE function in Set Analysis