Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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:
And this document: Calculating rolling n-period totals, averages or other aggregations
Use this: Accumulations
Hi,
It works with the models sent.
Thank you so much!
Hi,
It works with the models sent.
Thank you so much!
Great. If that answers you question please mark this discussion as answered.