Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
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

Hi,

Your  expression should be

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

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
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)