5 Replies Latest reply: Oct 31, 2011 12:30 PM by Stefan Wühl RSS

    % of sales comparated with other product...

      Hi:

      I Want to create a pivot table with two expressions, one for the total sales by product (first dimension) and the second for the % that the product in the current  dimension correspond to other specific Product (in my case Product A).

      I have been trying with aggr and with two diferent tables. Im adding my sample project...

      Thanks in advance.

       

      ProuductSum(Sales)
      % of respect Prodduct A

      Product A100100%
      Product B1010%
      Product C3030%
      Product D1010%
      Product E2020%
        • % of sales comparated with other product...

          In my sample I Have only one dimension but i need arround 5 more dimensions... I was trying something like this: aggr(sum({  $<  Producto = {'Prod_2'} > }    Cantidad ),Producto,Mes) and something like this: sum({  $<  Producto = {'Prod_2'} > }    TOTAL Cantidad ), the problem is that doesnt filter the content of the other dimensions...

          • % of sales comparated with other product...

            Hi:

            This solution has a strenge use, if I Collapse the dimension asociated to "Product-2" all the calculations in other dimensions are null or 0, if i exapnd it then it works fine...

            Do you have any idea to solve this?

              • % of sales comparated with other product...
                Stefan Wühl

                It seems that when you collapse the dimension, QV doesn't have the Mes values for that particular product-2 anymore. Don't know why, sometimes I think it's really not well designed.

                 

                you could introduce advanced aggregation to overcome this, but then I don't get the subtotals right anymore.

                 

                So I came up with a solution which checks for dimensionality and uses either above expression with or without advanced aggegration (Maybe there is a much more simple solution, I don't know):

                 

                =if(dimensionality()>1,

                aggr(sum(Cantidad) / Sum(total<Mes> {  $<  Producto = {'Prod_2'} > } Cantidad), Producto, Mes),

                sum(Cantidad) / Sum(total<Mes> {  $<  Producto = {'Prod_2'} > } Cantidad)

                )

                 

                The check for dimensionality was designed for the table you attached in your OP, if you add more dimensions, you might need to adjust for that.

                 

                Hope this helps,

                Stefan