Paul Kelly wrote: I think that this may be the reason I am getting what I am getting with above...
Yes, that's why you're getting what you're getting with above(). Above() in a pivot table refers to the current "column segment". The column segment is all values of the last dimension in the context of a single value of the previous dimension, if I remember correctly.
In Excel at least, your Period field is the same as the month. It displays as 200910, while the month displays as Jan-10. Above can ONLY select months within the current period. The period is only a month. Therefore, the above() function can't return any more than the current cell, the current month.
Why is the period dimension there at all? It is more than a month in your actual chart?
Financial Year Period and MonthYear are there because some of our users prefer working with financial year period (200910) whilst some prefer to see the month year (January 2010) - trying to keep evryone happy...
I have playing around today trying to crack this with no success - the spreadsheet I attached ealier "QlikView Example.xls" (Feb 10 @ 8:36) shows what I am trying to achieve...
Set analysis may solve this for me but we will not be upgrading to version 9 for at least another 6 weeks...
Any assistance in cracking this would be grateful...
How about creating a cyclic group with Financial Year Period and MonthYear in it, and use the cyclic group as the dimension? Then the users can select which one they want to use, and as a side effect, it should cure your rolling 12-month headache.
And no, set analysis won't crack it. A set is calculated once for the entire chart. So if you make a "last 12 month" set, every row/column in your chart will use the SAME 12 months. You could crack it with a month island, but I think that's the wrong solution.
Bah! I didn't think above(), below(), before() and after() were sensitive to the orientation of the pivot chart itself. I thought they were fixed based simply on the order of the dimensions. I was wrong.
So, in this case, your months are sorted in descending order, and they're laid out horizontally. Therefore, you need to use after() instead of above() to pick up the data in the columns that occur after this one. I suppose that's more intuitive, but it leads to data that changes if you start pivoting.
So I got the results with:
rangeavg(after(sum(sin_Sales),0,12))
Since it can compute an average even with less than 12 months, it does, so if you really want zero for the first eleven months, you'll have to play with it further.
Thanks John - you have made a young man very happy....
I have a calendar sequence field in my live report - can probably say if this is less than 12 do not calculate to prevent this appearing if below 12 months...
Just a note to thank you for your post/solution on this subject. I was beginning to think what I required was impossible but this pointed me in correct direction. I hadn't realised how powerfull the combination of range and inter-record functions could be.
Not the first time one of your posts has helped me out.
