Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
/
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?
How about this
Sum({<ZeroSurgicalUsedWastedIND = {'No'}, ProcGroupDesc = {"*Fracture*"}>} SupplyChainLUOMTotalCost)
/
Aggr(NODISTINCT
Sum({<ZeroSurgicalUsedWastedIND = {'No'}, ProcGroupDesc = {"*Fracture*"}>} SupplyChainLUOMTotalCost)
, ProcedureSurgeonName)
Hi,
Your expression should be
SUM({<ZeroSurgicalUsedWastedIND = {'No'},ProcGroupDesc = {"*Fracture*"}>}SupplyChainLUOMTotalCost)/SUM({<ZeroSurgicalUsedWastedIND = {'No'},ProcGroupDesc = {"*Fracture*"}>}TOTAL <ProcedureSurgeonName>SupplyChainLUOMTotalCost)
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.
How about this
Sum({<ZeroSurgicalUsedWastedIND = {'No'}, ProcGroupDesc = {"*Fracture*"}>} SupplyChainLUOMTotalCost)
/
Aggr(NODISTINCT
Sum({<ZeroSurgicalUsedWastedIND = {'No'}, ProcGroupDesc = {"*Fracture*"}>} SupplyChainLUOMTotalCost)
, ProcedureSurgeonName)
Thank you Sunny,
Any chance you can explain to me why Aggr(NODISTINCT works and creating the Aggr the other way didn't?
Where else did you try Aggr() within your set analysis?
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)