Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
NickHoff
Specialist
Specialist

Percent Total on a ranked dimension

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)

 

surgeonvendor.png

 

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.  Percentage of total.png

 

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)

/

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

 

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

1 Solution

Accepted Solutions
sunny_talwar

How about this

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

View solution in original post

6 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

Your  expression should be

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
NickHoff
Specialist
Specialist
Author

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.

sunny_talwar

How about this

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

Thank you Sunny,

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

sunny_talwar

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

NickHoff
Specialist
Specialist
Author

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

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

/

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