Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I would like to display the results of the following statements in one column (PercentileRank) on a table, how do I combine them into one set analysis statement?
=if(Sum(TotalCharge)>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.9),'90')
=if(Sum((TotalCharge))<fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.9)
and (Sum(TotalCharge))>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.8),'80')
=if(Sum((TotalCharge))<fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.8)
and (Sum(TotalCharge))>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.7),'70')
=if(Sum((TotalCharge))<fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.7)
and (Sum(TotalCharge))>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.6),'60')
The results for this field will be 90, 80, 70, 60
This is similar to case when statements in SQL.. but I am not sure what the right format is to work in set analysis. Thank you in advance for your help.
Nevermind.. I figure it out.. here is the correct answer just in case anybody else is interested
// 90thPercentileRank
=if(Sum(TotalCharge)>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.9),'90',
// 80thPercentileRank
if(Sum((TotalCharge))<fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.9) and (Sum(TotalCharge))>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.8),'80',
// // 70thPercentileRank
if(Sum((TotalCharge))<fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.8) and (Sum(TotalCharge))>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.7),'70',
// 60thPercentilRank
if(Sum((TotalCharge))<fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.7) and (Sum(TotalCharge))>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.6),'60',
// 50thPercentileRank
if(Sum((TotalCharge))<fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.6) and (Sum(TotalCharge))>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.5),'50',
// 40thPercentileRank
if(Sum((TotalCharge))<fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.5) and (Sum(TotalCharge))>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.4),'40',
// 30thPercentileRank
if(Sum((TotalCharge))<fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.4) and (Sum(TotalCharge))>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.3),'30',
// 20thPercentileRank
if(Sum((TotalCharge))<fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.3) and (Sum(TotalCharge))>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.2),'20',
// 10thPercentileRank
if(Sum((TotalCharge))<fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.2) and (Sum(TotalCharge))>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.1),'10','<10')))))))))
Nevermind.. I figure it out.. here is the correct answer just in case anybody else is interested
// 90thPercentileRank
=if(Sum(TotalCharge)>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.9),'90',
// 80thPercentileRank
if(Sum((TotalCharge))<fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.9) and (Sum(TotalCharge))>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.8),'80',
// // 70thPercentileRank
if(Sum((TotalCharge))<fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.8) and (Sum(TotalCharge))>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.7),'70',
// 60thPercentilRank
if(Sum((TotalCharge))<fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.7) and (Sum(TotalCharge))>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.6),'60',
// 50thPercentileRank
if(Sum((TotalCharge))<fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.6) and (Sum(TotalCharge))>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.5),'50',
// 40thPercentileRank
if(Sum((TotalCharge))<fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.5) and (Sum(TotalCharge))>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.4),'40',
// 30thPercentileRank
if(Sum((TotalCharge))<fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.4) and (Sum(TotalCharge))>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.3),'30',
// 20thPercentileRank
if(Sum((TotalCharge))<fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.3) and (Sum(TotalCharge))>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.2),'20',
// 10thPercentileRank
if(Sum((TotalCharge))<fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.2) and (Sum(TotalCharge))>=fractile(TOTAL aggr(sum(TotalCharge),[BillingProvider]),.1),'10','<10')))))))))