Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Display multiple if statements results in one column

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.

1 Solution

Accepted Solutions
Not applicable

Re: Display multiple if statements results in one column

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

1 Reply
Not applicable

Re: Display multiple if statements results in one column

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

Community Browser