Period performance in pivot

[Anonymous]

I'm trying to calculate investment performance for the selected perdiod in a pivot table, based on balances and cashflows in the underlying data.

Having "ASSET_CLASS" and "SUB_ASSET_CLASS" as vertical dimensions and "TODATE" as vertical Dimension, I'm able to calculate the performance at each "TODATE"  since the start of the period, using the formula:

RANGESUM(before(LOG(1+(SUM(BALANCE)-SUM(PREV_BALANCE)+SUM(CASHFLOW))/SUM(PREV_BALANCE)),0,ColumnNo()))

So far, so good. However; What I realy want to show is just the performance at the last date, rather than as now at each "TODATE".

E.g. in stead of:

Asset Class	Sub asset class	31.12.2015	04.01.2016	05.01.2016	06.01.2016	07.01.2016	08.01.2016
Equities		100,00%	98,92%	98,95%	98,48%	97,29%	97,01%
	Europe	100,00%	99,39%	99,44%	98,86%	97,91%	97,51%
	US	100,00%	96,97%	96,95%	96,95%	94,77%	94,97%
Bonds		100,00%	100,03%	100,08%	100,06%	100,01%	100,00%
	Government	100,00%	100,01%	100,01%	100,01%	100,01%	100,03%
	Corporate	100,00%	100,04%	100,11%	100,09%	100,00%	99,98%
Total		100,00%	99,72%	99,77%	99,63%	99,24%	99,16%

I would like to just see

Asset Class	Sub asset class	08.01.2016
Equities		97,01%
	Europe	97,51%
	US	94,97%
Bonds		100,00%
	Government	100,03%
	Corporate	99,98%
Total		99,16%

Using the aggregate function, I'm halfway there as it returns the correct numbers for the sub asset classes. For the PArtial and total sums though it sums the results on the sub asset classes rather than summing the partial sums for each date.

SUM(AGGR(log(1+(SUM(BALANCE)-Sum(PREV_BALANCE)+SUM(CASHFLOW))/Sum(PREV_BALANCE)),TODATE, ASSET_CLASS, SUB_ASSET_CLASS))

Hence if it were possible to force the partial sums in the aggregated expression to be the sum of the partial sums in the period, or have the pivot just show the last date in the pivot without using the aggregate function, both would work, but unfortunatley I can not seem to find a way to do that.

Anybody have a suggestion on how to do this? I'd prefer not to calculate it in the load as i would like the indexing to and grouping to be dynamic depending on how the user sets its filters on asset classes and dates.

[sunny_talwar]

How about doing this:

LOG(1+(SUM(BALANCE)-BEFORE(SUM(BALANCE))+SUM(CASHFLOW))/BEFORE(SUM(BALANCE))) * Avg({<TODATE = {"$(=Date(Max(TODATE), 'DD.MM.YYYY'))"}>}1)

[sunny_talwar]

I am assuming that the format of your TODATE is DD.MM.YYYY. If it is something else, then replace the format within the Avg() expression to match with the format of TODATE

[sunny_talwar]

Alternatively, you can try this:

LOG(1+(SUM({<TODATE = {"$(=Date(Max(TODATE), 'DD.MM.YYYY'))"}>}BALANCE)-BEFORE(SUM({<TODATE = {"$(='>=' & Date(Max(TODATE) - 1, 'DD.MM.YYYY'))"}>}BALANCE))+SUM({<TODATE = {"$(=Date(Max(TODATE), 'DD.MM.YYYY'))"}>}CASHFLOW))/BEFORE(SUM({<TODATE = {"$(='>=' & Date(Max(TODATE) - 1, 'DD.MM.YYYY'))"}>}BALANCE)))