Rolling 12 months dynamic based on selection - aggr

[#1]

Hi all

I wish to show a rolling 12 month revenue graph - always showing 12 months in the dimention with a sum of the past 12 months billing for each month. I am getting stuck on two aspects:

1. How to display the MonthYear (text) as the dimension - limited to 12 months, rather than the numeric value (MonthYearSerial).

2. How to make this dynamic in terns of selection one MonthYear (from calendar), but still showing the selected moth plus the other 11 months in the graph.

I am using the aggr function with the range sum function - whcih works when no selections are made, but iealy I want the results to show as per above when various MonthYear selections are made. Below is my expresion and I ahve attacehd an example app with my data and objects.

sum(aggr(rangesum(above(total sum({<MonthYearSerial = {"<=$(=Max(MonthYearSerial))"}>}Billed_Amount_GBP),0,12)),MonthYearSerial))

Any assistance will be greatly appreciated!

Thx

[MK_QSL]

Calculating rolling n-period totals, averages or other aggregations

[#3]

Hi Scott

Maybe it is useful to have a simple expression and use a dynamic dimension for your problem.

I managed this by using the following dimension:

=Dual(if( YOURYEARMONTH >=  $(v12Month) AND YOURYEARMONTH <= $(vCurMonth), YOURYEARMONTH ),YOURYEARMONTH )

The variables are calculated like this

vCurMonth   =Date(Max(YOURDATE),'YYYYMM')

v12Month     =Date(addmonths(Max(YOURDATE),-11),'YYYYMM')

In your expressions you may use simple sums but you need to exclude the filters for YOURYEARMONTH, MONTH and YEAR via Set Analysis.

something like this:

Sum({$<SICHTS={'Linie','Charter'},STATUS={'D'}, JahrMonat=,Jahr=,Monat=>}PAX)

Let me know if this is helping you.

Kind regards,

Anthony