Moving Annual Total % of oldest month!

[pkelly]

Hi all

I have a moving annual total chart which shows the MAT for sales and gross profir (sales calculation below)...

=rangesum(above(sum(mat_Sales),0,12))

What I have now been asked to show is the moving average total as a percentage of the oldest month in my chart.

I can decipher the oldest months via the field mat_HistoricalMonthNumber = 14.

However, if I try and use my existing formula and divide by the oldest month I will obvioulsy screw up the rest of the chart as I am using rangesum / above to get my MAT...

Anyone any ideas?

Thanks

Paul

[christian77]

Hi:

I don´t understand very well.

If you want to divide every value on a column by one determinate value in a table, you can have that determinate value doing:

top(total(column(3)))        Top (upper) value in column 3

top(total(column(3)),5)    Fifth value from the top in column 3

top(total(column(3)),noofrows(Total))  Last value from the top, no matter how many

bottom(total(column(3)))      Exactly the same as above.

They are many combinations

[pkelly]

Hi ChristianI have posted an example of what I am trying to do...

[pat_agen · Accepted solution]

hi,

see attached qvw.

Christian pointed you in the right direction: the Top() function. The offset is hardcoded to 12 but this makes sense giving that you are going after an MAT.

Have added a cyclic group to your graph so you can toggle between extended values and the Base100 index.

[pkelly]

Thanks for your help guys...

[christian77]

Hi:

I´m sending something I think could be good. It is more dificult in the line chart. I did it with a variable also.