topic Re: Accumulating value in PivotTable in QlikView

Accumulating value in PivotTable

claudio_reis_we — Mon, 14 Dec 2015 17:27:56 GMT

Hello!

I'm having trouble accumulating values in a PivotTable.

In the example below, the proposal is to build value for the current period + N months as the user's selection.

Ex. When N = 3, adds Jan 2015 = 10.00 + 20.00 + = Feb 2015 Mar 2015 = 30.00, Accumulated Future (Jan 2015) = 60.00.

In this model, I understand you have to work with set analysis, because the user can make the selection period and also because they

have other dimensions.

Date Period Accumulated Future Value

01/01/2015 Jan 2015 10.00 60.00

01/02/2015 Feb 2015 20.00 90.00

01/03/2015 Mar 2015 30.00 120.00

01/04/2015 Apr 2015 40.00 90.00

01/05/2015 May 2015 50.00 50.00

I tried with the following expression, but only works if you make a selection period without selecting presents the sum total of the value column for all periods.

sum ({$ <Date = {'> = $ (Data) <= $ (= AddMonths (Date, 2))'}>} Value)

Can please help me?

Thanks,

Re: Accumulating value in PivotTable

Gysbert_Wassenaar — Mon, 14 Dec 2015 17:39:53 GMT

The set of a set analysis expression is calculated at the chart level, not the row level. That's probably why what you're trying isn't working. See these blog posts:

Accumulations‌

The As-Of Table

And this document: Calculating rolling n-period totals, averages or other aggregations

Re: Accumulating value in PivotTable

Clever_Anjos — Mon, 14 Dec 2015 17:49:28 GMT

Use this: Accumulations

Re: Accumulating value in PivotTable

claudio_reis_we — Tue, 15 Dec 2015 16:15:28 GMT

Hi,

It works with the models sent.

Thank you so much!

Re: Accumulating value in PivotTable

claudio_reis_we — Tue, 15 Dec 2015 16:15:54 GMT

Hi,

It works with the models sent.

Thank you so much!

Re: Accumulating value in PivotTable

Gysbert_Wassenaar — Tue, 15 Dec 2015 16:21:15 GMT

Great. If that answers you question please mark this discussion as answered.