topic Re: Rolling 12 months dynamic based on selection - aggr in QlikView

Rolling 12 months dynamic based on selection - aggr

Fri, 15 Aug 2014 10:48:36 GMT

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

Re: Rolling 12 months dynamic based on selection - aggr

MK_QSL — Fri, 15 Aug 2014 11:00:58 GMT

Calculating rolling n-period totals, averages or other aggregations

Re: Rolling 12 months dynamic based on selection - aggr

Tue, 01 Dec 2015 08:32:56 GMT

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