Expressions in Pivot with different dimensions

[#1]

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.

[#2 · Accepted solution]

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), ', ')

[#3]

Hi Nicolas,

you are right! Many thanks!