topic Re: Trailing 12 that includes values not in chart in QlikView

Trailing 12 that includes values not in chart

Tue, 28 Oct 2014 18:21:20 GMT

Hi All,

I have a need for calculating the trailing 12 accumulation of production. I can do this in a chart using the following:

RangeSum(Above( SUM([Production]),0 ,12))

However, it starts with the first value in the chart, and it isn't really accurate until the 12th month. I need the ability to start at the first month with the T12 of the prior months that came before. I cannot include the prior months in the chart - not even with the scroll bar trick.

Is there a set analysis expression that might work?

Thanks,

Blair

Re: Trailing 12 that includes values not in chart

Kushal_Chawda — Tue, 28 Oct 2014 18:25:04 GMT

Please go through below link

http://community.qlik.com/docs/DOC-4252

Re: Trailing 12 that includes values not in chart

Tue, 28 Oct 2014 18:42:26 GMT

I looked at this post before posting. It looks quite simple when the data is pre-aggregated. In my case, the data is not at the month grain, and requires a sum. Eventually, I need to use this expression in another calculation which is also a sum:

RangeSum(Above( SUM([Production]),0 ,12)) / Sum(Value)

Where the Value is from the same original fact table. It appears as if I would then need to add the expression in the denominator into the AsOfMonth table so that it is available in the chart. Is that correct? Is this the best way to do this - create another aggregated table in the data model?

Re: Trailing 12 that includes values not in chart

Tue, 28 Oct 2014 20:33:27 GMT

Update: I thought I might try the AsOf table approach, but soon realized that this may not be practical. Without aggregated data, my fact table is at approximately 75 million rows. Each month in the AsOf table would be the same size, meaning nearly a billion rows. Will this method scale up to this amount of data?