topic Re: Using Accumulate 12 steps back for MAT graph, but cant limit date range in QlikView

Using Accumulate 12 steps back for MAT graph, but cant limit date range

matthewp — Mon, 14 Mar 2016 10:46:57 GMT

I have a line chart which has two dimensions:

Yearperiod = 201601, 201602, 201603 etc etc

Acgroup = FAB, BOB, TOE, QUE etc

And one expression:

SUM(Quantity)

This also has Accumulate 12 steps back ticked.

Now the data i get in the chart is correct and is what i want my MAT to be.

However the line chart is showing all dates and i just want to limit it to the last 12.

I have tried doing this with a calculated dimension for Yearperiod like so:

=if(Yearperiod >= addmonths(today(),-6), Yearperiod )

But this didn't do anything.

marcus_sommer — Mon, 14 Mar 2016 11:57:43 GMT

With this approach you would need to convert and format your date which you get through addmonths() like:

... year(addmonths(today(),-6)) * 100 + num(month(addmonths(today(),-6)), '00') ... // only 6 months ?

another way might be:

aggr(if(Yearperiod >= max(Yearperiod) - 100, Yearperiod), Yearperiod) // 12 months

- Marcus

Gysbert_Wassenaar — Mon, 14 Mar 2016 12:52:22 GMT

Perhaps like this:

sum({<YearPeriod={'>=$(=AddMonths(max(YearPeriod),-11))'}>}aggr(rangesum(above(sum({<YearPeriod=>}Quantity),0,11)),YearPeriod))