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

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

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

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

• % of sales comparated with other product...

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

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

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