Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

Re: Percentage of TOTAL Sales

You could try

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

6 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Percentage of TOTAL Sales

Hi,

Try this

=Sum(Sales)/Sum(TOTAL Sales)

Regards,

Jagan.

Not applicable

Percentage of TOTAL Sales

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?

MVP
MVP

Re: Percentage of TOTAL Sales

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

Re: Percentage of TOTAL Sales

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

MVP
MVP

Re: Percentage of TOTAL Sales

You could try

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

Not applicable

Re: Percentage of TOTAL Sales

Many thanks, this does work ...