Display multiple if statements results in one column

Sat, 31 Aug 2013 16:29:07 GMT

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.

Re: Display multiple if statements results in one column

Sat, 31 Aug 2013 20:34:25 GMT

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')))))))))

topic Re: Display multiple if statements results in one column in QlikView

Display multiple if statements results in one column

Re: Display multiple if statements results in one column