2 Replies Latest reply: May 28, 2012 5:28 AM by Alessandro Tomè RSS

    Expressions in Pivot with different dimensions

      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
          Nicolas MARTIN

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

           

           

           

          2012-05-25_095333.png