topic Re: % to Gross Sales in QlikView

% to Gross Sales

tmumaw — Tue, 25 Feb 2014 19:24:30 GMT

I have a pivot table which has the following lines:

A: Gross Sales 2000.00

B: Base Payroll 500.00

C: Benefits 250.00

😧 Equipment 250.00

E: Materials 500.00

My user wants to know what % Base Payroll is to Gross Sales, what % Benefits are to Gross Sales, what % Equipment is to Gross Sales, what % Materials are to Gross Sales. Any ideas?

Thanks

Re: % to Gross Sales

Tue, 25 Feb 2014 19:27:52 GMT

Hi Thom

I think you can do it with Set Analysis.

For example Base Payroll to Gross:

SUM(measue_value) / SUM({<measure_name = {'Gross Sales'}>} measure_value)

Lukasz

Re: % to Gross Sales

MK_QSL — Tue, 25 Feb 2014 19:34:19 GMT

kindly provide some more dimension names as these data are not enough to give answer...

Re: % to Gross Sales

tmumaw — Tue, 25 Feb 2014 19:45:28 GMT

My dimension for the following is GL Hier 2

A : Gross Sales

B: Base Payroll

C: Benefits

😧 Materials
E: Equipment

Re: % to Gross Sales

Tue, 25 Feb 2014 20:03:37 GMT

I think Lukasz's answer is correct.

Just adapt the field names of the answer to your model to get it work.

Fabrice

Re: % to Gross Sales

aveeeeeee7en — Tue, 25 Feb 2014 20:13:27 GMT

Dear Thom

See the Attachment

Regards

Aviral Nag

Re: % to Gross Sales

Tue, 25 Feb 2014 20:31:21 GMT

try,

% base payroll to gross sales

num(sum([base payroll]) / (sum([gross sales]), '##%')

% benefits to gross sales

num(sum(benefits) / (sum([gross sales]), '##%')

%equipment

num(sum(equipments) / (sum([gross sales]), '##%')

%materials

num(sum(materials) / (sum([gross sales]), '##%')

Re: % to Gross Sales

tmumaw — Tue, 25 Feb 2014 22:05:04 GMT

This would be great if it wasn't a pivot table. My pivot table has 3 dimensions (GL Hier 2 (which is what I shared) , GL Hier 3 and GL Acct) with one measure GL Amount. I need to show these %'s at all levels of the pivot table, so I can't just name things like Gross Sales, Benefits, Equipment, etc..

Re: % to Gross Sales

tmumaw — Wed, 26 Feb 2014 13:05:44 GMT

Any ideas?

Re: % to Gross Sales

Wed, 26 Feb 2014 15:27:29 GMT

if u send me an example will the pivot table i will be able to help u