New to QlikView

Discussion board where members can get started with QlikView.

New Contributor III

Calculation of percentage in pivot

Hi,

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%

 AAPAC Avanade Category group Catalog Count% Contractors 11.95% 0.54% Excluded 1.69% 0.00% Facilities & Services 31.28% 4.44% Finance Services 0.00% 0.00% HR 21.48% 0.00% IT & Telecom 35.29% 4.77% Legal Services & Risk 0.37% 0.00% Marketing & Communications 4.78% 0.62% Prepaid 60.14% 0.00% Travel Services 22.46% 0.23% Unclassified 0.00% 0.00%
5 Replies MVP

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

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

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

Highlighted
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

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]='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