Cross dimension value reference

[#1]

Dear all,

I need to calculate sales commission for each region each year in a pivot table (whereby year and region are the dimensions).

There is a predefined commission fee ratio for each region each year and the commission fee is calculated as net sales * commission fee ratio.

For FY 13 I have some missing values in the field commission fee ratio so we'd like to estimate the commission fee for FY13 with the ratios of FY14.

My question is, how should I write the expression in the pivot table so that I can use the ratio of FY14 to calculate the commission fee for FY13? Or this kind of calculation must be done in scripting?  (there are 4 columns in total in the raw table, Year, Region, NetSales and CommissionFeeRatio).

Many thanks in advance!!

Best Regards,

Jenny

[marcus_sommer]

With interrecord-functions like Missing Manual - Before() and After() or Missing Manual - Above() and Below() you could access columns and rows within a table-chart.

- Marcus