Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

savithri_n_s
New Contributor III

Calculation of percentage in pivot

Hi,

Can you please help in calculating percentage as below

Attached the qvw file and also excel file of calculation

for example

 

Facilities & Services total PO line item volume under AAPAC : 1000

 

Facilities & Services PO lines on Catalog  under AAPAC: 200

 

Catalog count % for Facilities & Services- 20%

 

   

AAPACAvanade
Category groupCatalog Count%
Contractors11.95%0.54%
Excluded1.69%0.00%
Facilities & Services31.28%4.44%
Finance Services0.00%0.00%
HR21.48%0.00%
IT & Telecom35.29%4.77%
Legal Services & Risk0.37%0.00%
Marketing & Communications4.78%0.62%
Prepaid60.14%0.00%
Travel Services22.46%0.23%
Unclassified0.00%0.00%
5 Replies

Re: Calculation of percentage in pivot

Is above chart is the output you are looking to get or you are getting this, but looking to get something else? Not sure I fully understand what you want to get....

savithri_n_s
New Contributor III

Re: Calculation of percentage in pivot

yes sunny .i want the same calculation shown in table to be done in qvw file.we have the chart in qvw file however the calculation has to be modified

Highlighted
sibin_jacob
Contributor III

Re: Calculation of percentage in pivot

Hi Savithri,

Create a new straight table.

Dimension as (INV)Category Group

then create the expression as below.

sum({<[(REQ) Line Type] = {"Catalog Item"}>}[(REQ) sum(PO Spend)])/

(sum({<[(REQ) Line Type] = {"Catalog Item"}>}[(REQ) sum(PO Spend)])+sum({<[(REQ) Line Type] = {"Non-Catalog Item"}>}[(REQ) sum(PO Spend)]))

Thanks,

Sibin

sibin_jacob
Contributor III

Re: Calculation of percentage in pivot

Hi Savithri,


If you want to group the data into Region also, then create a pivot table.


Dimension:

(INV)Category Group

(INV)Region


then create the expression as below.

sum({<[(REQ) Line Type] = {"Catalog Item"}>}[(REQ) sum(PO Spend)])/

(sum({<[(REQ) Line Type] = {"Catalog Item"}>}[(REQ) sum(PO Spend)])+sum({<[(REQ) Line Type] = {"Non-Catalog Item"}>}[(REQ) sum(PO Spend)]))

Thanks,

Sibin


pooja_prabhu_n
Contributor III

Re: Calculation of percentage in pivot

Hi,

Try this without Total

=count({<[(REQ) Line Type] = {"Catalog Item"}>}[(REQ) Line Type])/

if([(INV)Region]='AAPAC',(count({<[(INV)Region]={'AAPAC'}>}[(REQ) Line Type])),

if([(INV)Region]='Avanade',(count({<[(INV)Region]={'Avanade'}>}[(REQ) Line Type])),

if([(INV)Region]='Europe',(count({<[(INV)Region]={'Europe'}>}[(REQ) Line Type])),

if([(INV)Region]='Global Entities',(count({<[(INV)Region]={'Global Entities'}>}[(REQ) Line Type])),

if([(INV)Region]='Latin America',(count({<[(INV)Region]={'Latin America'}>}[(REQ) Line Type])),

if([(INV)Region]='North America',(count({<[(INV)Region]={'North America'}>}[(REQ) Line Type])),

(count({<[(INV)Region]={'Unclassified'}>}[(REQ) Line Type]))))))))

Thanks,

Pooja