Percentage of TOTAL Sales

[#1]

Hi Experts,

I have the following table:

AGGR1 , AGGR2 ,    AGGR3, Amount

Sales    , Busines1 , Product11, 10

Sales    , Busines1 , Product12, 20

Sales    , Busines2 , Product21, 20

Sales    , Busines3 , Product31, 40

Expenses, Expenses, Expenses, 400

Promotion, Promotion, Promotion, 300

OOI,            OOI,          OOI,        200

I would like to build a QV table, showing me totals for AGGR1 not being Sales (this is easy), but as well showing me in respective rows, how much the given category is in relation to total Sales, so:

In case of expenses I would like to see total for AGGR1 = Expenses (This I know how to get) and then in next collumn, this figure divided by TOTAL SALES. I do not know how in the row containing expenses, can I get the TOTAL SALES figure (denominator of my %)..

Thanks a lot for your help

//Robert

[jagan]

Hi,

Try this

=Sum(Sales)/Sum(TOTAL Sales)

Regards,

Jagan.

[#3]

Hi,

Thanks for your answere, but this does not work... I was reading about Sum(TOTAL variable) construction and it does not fit to my need because:

- referring to my example table, I do not want to calculate how much "Business1" is in relation to total "Sales"

- I want to show how much the variable not being part of SALES (expenses for instance) are in relation to TOTAL
SALES. I would like to build a cross tab with AGGR1 as first dimension, then

sum ({$<[AGGR1] -= {'SALES'} > Amount}

as second dimension and percentage of items not having in AGGR1 "Sales" to total sum of those items having in AGGR1 "Sales" as 3rd column of my table...

Please, anyone has any idea?

[swuehl]

Robert,

not sure if you really mean that you want sum ({$<[AGGR1] -= {'SALES'} > Amount}

as second dimension, I think you need advanced aggregation then, like

=aggr(sum ({$<[AGGR1] -= {'Sales'} >} Amount), AGGR1)

Anyway, I believe you can call the sales amount in any cell using dollar sign expansion, something like

=column(1) / $(=sum({<AGGR1= {Sales}>}Amount))

See also attached.

[#5]

Dear swuehl,

Very helpful answere for me - thanks a lot. I did not know this $ sign on the beginning of statement making the trick.

Additional question - I would like to have the table attached in your example split per country. Having 4 countries, (So 4 columns for expenses, and 4 columns for ratio) , in each column with ratio I get the total sales for all the countries, and not total sales for a given country... I was trying to modify your formula like that:

$(=sum({<AGGR1= {Sales}, Country = {PL, CZ, SK, HU}>}Amount))

thinking that this helps, but no change. Still I get one figure (total sales for all countries) for all the columns.

Could you please help me sovve this one?

//Robert

[swuehl · Accepted solution]

You could try

=column(1) / sum({<AGGR1= {Sales}>} total<Country> Amount)

[#7]

Many thanks, this does work ...