Problem on pivot and dimensionality

Tue, 03 Aug 2010 16:50:51 GMT

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

Problem on pivot and dimensionality

Fri, 06 Aug 2010 12:42:10 GMT

Hi,

any idea?

Andrea

topic Problem on pivot and dimensionality in QlikView

Problem on pivot and dimensionality

Problem on pivot and dimensionality