2 Replies Latest reply: Jul 4, 2012 4:10 AM by Nicolas MARTIN RSS

    Problem with "total" in pivot table when collapsed

    Nicolas MARTIN

      Hello,

       

      I have a problem with a formula in a pivot table.

       

      I want to display the market share of the following data:

       

       

      LOAD * INLINE [
      Market, Product, Country, Sales
      Drinks, Coca Cola, USA, 90
      Drinks, Coca Cola, Canada, 10
      Drinks, Water, USA, 40
      Drinks, Water, Canada, 10
      Food, Burger, USA, 180
      Food, Burger, Canada, 20
      Food, Fries, USA, 40
      Food, Fries, Canada, 10
      ];
      
      

       

      I use this expression to know the values of the whole market:

       

      sum(total <Market, Country> Sales)
      

       

       

      If all my column are expanded, I have the right values:

      2012-07-03_124637.png

       

       

      But if I expand only 1 value of column, my values are wrong:

      2012-07-03_124850.png

       

      See, It says that the market is "10" and "90" whereas it should be "20" and "130".

       

      I don't understand why my values are not the same when I expand / collapse 1 or more values in the columns...

       

       

       

       

       

      I've found a workaround by using the "aggr" function:

       

      if(Dimensionality() = 3,
                aggr(nodistinct sum(total <Market, Country> Sales), Market, Country),
                if(Dimensionality() = 2,
                          aggr(nodistinct sum(total <Market, Country> Sales), Market),
                          sum(total <Market> Sales)
                )
      )
      
      

       

      but it's very hard to use / update when I have more than 5 columns.

       

      Is there a better way?

       

       

      Is there a way to do the same without having the "Market" in dimension and without make agregation in load?

       

       

       

      Thank you.