Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Percentage of TOTAL Sales

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

You could try

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

View solution in original post

6 Replies
jagan
Luminary Alumni
Luminary Alumni

Hi,

Try this

=Sum(Sales)/Sum(TOTAL Sales)

Regards,

Jagan.

Not applicable
Author

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

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.

Not applicable
Author

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

You could try

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

Not applicable
Author

Many thanks, this does work ...