Cross dimension value reference

Mon, 12 Dec 2016 18:57:15 GMT

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

Re: Cross dimension value reference

marcus_sommer — Tue, 13 Dec 2016 08:51:52 GMT

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

topic Cross dimension value reference in QlikView

Cross dimension value reference

Re: Cross dimension value reference