Problem on pivot and dimensionality

[#1]

Hi to all,

I have the following scenario:

1 table:

Date, BrickCode, Product, Qty, Value, ProdGroup

201004;08010;DURIC;62;882.20;MKT PRODOTTI

201004;08011;DURIC;25;457.39;MKT PRODOTTI

201004;08012;DURIC;42;561.93;MKT PRODOTTI

201004;08101;DURIC;139;1903.17;MKT PRODOTTI

201004;08102;DURIC;87;1329.71;MKT PRODOTTI

201004;08103;DURIC;16;271.55;MKT PRODOTTI

out of this I create right(Date, 2) as M

2 table:

BrickCode, Aream, SalesRep

08010;AA;REP1

08011;AA;REP1

08012;BB;REP2

08101;BB;REP2

08102;CC;REP3

08103;CC;REP3

Now I have a pivot table with Aream, SalesRep and BrickCode and Product as dimension (the product is the column) and this formula:

if(dimensionality()=0, Sum (Value) / sum(TOTAL <M, AREAM> Value),

if(dimensionality()=1, Sum (Value) / sum(TOTAL <M, AREAM> Value),

if(dimensionality()=2, sum (Value) / sum(TOTAL <M, AREAM, SalesRep> Value),

if(dimensionality()=3, sum(Value) / sum(TOTAL <M, AREAM, SalesRep, BrickCode> Value)

))))

If I filter by month my formula works fine but if i sort by SaleRep or Product the result of the formula went wrong.

In fact the formula is the value of the whole sales of the product in the period selected divided by the sales for aream, salesrep or brickcode, depending on the level where i'm looking in the pivot table.

What i need to achive is to keep the result that i have without filter on the dimension also if i filter for one rep or one product.

Thank to all for help!

Andrea

[#2]

Hi,

any idea?

Andrea