topic Re: Percent Total on a ranked dimension in QlikView

Percent Total on a ranked dimension

NickHoff — Wed, 25 Nov 2020 16:16:04 GMT

My pivot table shown below has two dimensions, with the first being the ProcedureSurgeonName.

I have the following expression calculating my ranked dimension, which is the second dimension.

aggr(if(rank((SUM({<ZeroSurgicalUsedWastedIND = {'No'}>}SupplyChainLUOMTotalCost)))<=11,VendorName),ProcedureSurgeonName, VendorName)

My expressions are the following:

Supply Total Cost: SUM({<ZeroSurgicalUsedWastedIND = {'No'},ProcGroupDesc = {"*Fracture*"}>}SupplyChainLUOMTotalCost)

and

Percent of Total: SUM({<ZeroSurgicalUsedWastedIND = {'No'},ProcGroupDesc = {"*Fracture*"}>}SupplyChainLUOMTotalCost)/SUM({<ZeroSurgicalUsedWastedIND = {'No'},ProcGroupDesc = {"*Fracture*"}>}TOTAL SupplyChainLUOMTotalCost)

As you can see in the table above my percentage of total is displaying across all Physician's so i've changed it to aggr on the Vendor Name, using the following.

SUM({<ZeroSurgicalUsedWastedIND = {'No'},ProcGroupDesc = {"*Fracture*"}>}SupplyChainLUOMTotalCost)/SUM({<ZeroSurgicalUsedWastedIND = {'No'},ProcGroupDesc = {"*Fracture*"}>}TOTAL <VendorName>SupplyChainLUOMTotalCost)

But, my totals still aren't displaying properly, it should be 100% for each Physician total and then each vendor will add up to the 100%. I'm using the calculated dimension above for the vendor, but this is how my totals display when aggr by vendor.

I've also tried the following which yields no results:

SUM({<VendorName = {"=aggr(if(rank((SUM({<ZeroSurgicalUsedWastedIND = {'No'}>}SupplyChainLUOMTotalCost)))<=11,VendorName),ProcedureSurgeonName, VendorName)"},ZeroSurgicalUsedWastedIND = {'No'},ProcGroupDesc = {"*Fracture*"}>}SupplyChainLUOMTotalCost)

How can I get the % of Total column to display the percentages by my calculated dimension?

Re: Percent Total on a ranked dimension

kaushiknsolanki — Sat, 29 Feb 2020 06:35:51 GMT

Hi,

Your expression should be

Re: Percent Total on a ranked dimension

NickHoff — Mon, 02 Mar 2020 15:56:02 GMT

I tried that as well and it gives 100% for every vendor, i think the issues is just doing an aggregation on the TOTAL won't work because it's a ranked calculated dimension.

Re: Percent Total on a ranked dimension

sunny_talwar — Mon, 02 Mar 2020 19:20:32 GMT

How about this

Sum({<ZeroSurgicalUsedWastedIND = {'No'}, ProcGroupDesc = {"*Fracture*"}>} SupplyChainLUOMTotalCost) / Aggr(NODISTINCT Sum({<ZeroSurgicalUsedWastedIND = {'No'}, ProcGroupDesc = {"*Fracture*"}>} SupplyChainLUOMTotalCost) , ProcedureSurgeonName)

Re: Percent Total on a ranked dimension

NickHoff — Mon, 02 Mar 2020 23:13:58 GMT

Thank you Sunny,

Any chance you can explain to me why Aggr(NODISTINCT works and creating the Aggr the other way didn't?

Re: Percent Total on a ranked dimension

sunny_talwar — Tue, 03 Mar 2020 12:23:18 GMT

Where else did you try Aggr() within your set analysis?

Re: Percent Total on a ranked dimension

NickHoff — Tue, 03 Mar 2020 14:13:23 GMT

I tried using the following which the aggr was inside the set instead of outside, which that makes sense.