% of sales comparated with other product...

[#1]

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.

Prouduct	Sum(Sales)	% of respect Prodduct A
Product A	100	100%
Product B	10	10%
Product C	30	30%
Product D	10	10%
Product E	20	20%

[#2]

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...

[swuehl · Accepted solution]

Try this for share of product's sum(Cantidad) compared to Prod_2:

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

If you add more dimensions, you need to add those also to the total fields (like total<Mes,AnotherField> )

Hope this helps,

Stefan

[#4]

Thank you very mouch for your  help, I really appreciate it.

[#5]

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?

[swuehl]

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