topic Expressions in Pivot with different dimensions in QlikView

Expressions in Pivot with different dimensions

Thu, 24 May 2012 15:55:30 GMT

Hi,

I have two pivot tables. The first one shows the number of item sold by item and model. The second pivot shows the price of each item by model.

The first pivot uses the model from orders, while the second pivot uses the model from list.

All is ok, but what if I want to create only one pivot showing all two measures (number of items sold and price) by item and model for all items and models (i.e. a matrix). The values are wrong (i.e. ART (2070) is sold in NUVOLAX model and not in LUNAX model).

See attached qvw.

Can someone help me?

Thanks.

Re: Expressions in Pivot with different dimensions

Fri, 25 May 2012 07:57:05 GMT

In your model, there is relations between "ModelloTestata" and "ModelloDP".

If you use 1 of this dimension in your chart, every expression will consider this relation.

I advice you to use a detached dimension that contains the distinct values of the both dimensions, and use it as dimension in your graph.

DistinctModelli_TMP:
LOAD ModelloDP AS DistinctModello_TMP,
DesModelloDP AS DistinctDesModello_TMP
RESIDENT ModelliDP;
CONCATENATE(DistinctModelli_TMP)
LOAD ModelloTestata AS DistinctModello_TMP,
DesModelloTestata AS DistinctDesModello_TMP
RESIDENT ModelliTestata;
DistinctModelli:
LOAD DISTINCT DistinctModello_TMP AS DistinctModello,
DistinctDesModello_TMP AS DistinctDesModello
RESIDENT DistinctModelli_TMP
ORDER BY DistinctModello_TMP ASC;
DROP TABLE DistinctModelli_TMP;

Use the "DistinctDesModello" dimension in your graph, and usee a "if" condition in your expression:

sum(if(DistinctModello = ModelloTestata, Qta))

And

concat(if(DistinctModello = ModelloDP, DeltaPunti), ', ')

Expressions in Pivot with different dimensions

Mon, 28 May 2012 09:28:39 GMT

Hi Nicolas,

you are right! Many thanks!