Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Rolling 12 months dynamic based on selection - aggr

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

2 Replies
MVP
MVP

Re: Rolling 12 months dynamic based on selection - aggr

Not applicable

Re: Rolling 12 months dynamic based on selection - aggr

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