Hey Guys Can some one help or give idea about sorting Age Bucket in Character

[gopal5879]

Age Bucket is as string data type.

Question :

The business wants to sort by as below

0

1

2 - 5

16 - 29

30 - 44

45 - 60

61 or more

How can I achieve this, please any help appreciated.

Dimension ( AgeBucket in Days) :

= if( Aggr(FirstSortedValue(  {< CATEGORY= {'OPEN'}, DATEOUT_SELECT={'$(vQOutStandingDate)'} >}  QUEUEAGE,  -QUEUEAGE),WORKFLOWRTNGKEY ) = 0 , '0',
if(Aggr(FirstSortedValue(  {< CATEGORY= {'OPEN'}, DATEOUT_SELECT={'$(vQOutStandingDate)'}  >} QUEUEAGE,  -QUEUEAGE),WORKFLOWRTNGKEY ) = 1, '1',
if(Aggr(FirstSortedValue(  {< CATEGORY= {'OPEN'}, DATEOUT_SELECT={'$(vQOutStandingDate)'} >}  QUEUEAGE,  -QUEUEAGE),WORKFLOWRTNGKEY ) >=2
    AND Aggr(FirstSortedValue(  {< CATEGORY= {'OPEN'}, DATEOUT_SELECT={'$(vQOutStandingDate)'} >}  QUEUEAGE,  -QUEUEAGE),WORKFLOWRTNGKEY ) <=5, '2 - 5',
if(Aggr(FirstSortedValue(  {< CATEGORY= {'OPEN'}, DATEOUT_SELECT={'$(vQOutStandingDate)'}  >} QUEUEAGE,  -QUEUEAGE),WORKFLOWRTNGKEY ) >=6
    AND Aggr(FirstSortedValue(  {< CATEGORY= {'OPEN'}, DATEOUT_SELECT={'$(vQOutStandingDate)'} >}  QUEUEAGE,  -QUEUEAGE),WORKFLOWRTNGKEY ) <=15, '6 - 15', 
if(Aggr(FirstSortedValue(  {< CATEGORY= {'OPEN'}, DATEOUT_SELECT={'$(vQOutStandingDate)'} >}  QUEUEAGE,  -QUEUEAGE),WORKFLOWRTNGKEY ) >=16
    AND Aggr(FirstSortedValue(  {< CATEGORY= {'OPEN'}, DATEOUT_SELECT={'$(vQOutStandingDate)'} >}  QUEUEAGE,  -QUEUEAGE),WORKFLOWRTNGKEY ) <=29, '16 - 29',  
if(Aggr(FirstSortedValue(  {< CATEGORY= {'OPEN'}, DATEOUT_SELECT={'$(vQOutStandingDate)'} >}  QUEUEAGE,  -QUEUEAGE),WORKFLOWRTNGKEY ) >=30
    AND Aggr(FirstSortedValue(  {< CATEGORY= {'OPEN'}, DATEOUT_SELECT={'$(vQOutStandingDate)'} >}  QUEUEAGE,  -QUEUEAGE),WORKFLOWRTNGKEY ) <=44, '30 - 44',           
if(Aggr(FirstSortedValue(  {< CATEGORY= {'OPEN'}, DATEOUT_SELECT={'$(vQOutStandingDate)'}  >} QUEUEAGE,  -QUEUEAGE),WORKFLOWRTNGKEY ) >=45
    AND Aggr(FirstSortedValue(  {< CATEGORY= {'OPEN'}, DATEOUT_SELECT={'$(vQOutStandingDate)'} >}  QUEUEAGE,  -QUEUEAGE),WORKFLOWRTNGKEY ) <=60, '45 - 60',
if(Aggr(FirstSortedValue(  {< CATEGORY= {'OPEN'}, DATEOUT_SELECT={'$(vQOutStandingDate)'}  > !@@@} QUEUEAGE,  -QUEUEAGE),WORKFLOWRTNGKEY ) >=60,'61 or more'
))))))))   

Expression : OpenQueueAgeInBuckets

=Count(DISTINCT {< CATEGORY= {'OPEN'}, DATEOUT_SELECT={'1/1/1964'} >} WORKFLOWRTNGKEY)

Question :

The business wants to sort by as below

0

1

2 - 5

16 - 29

30 - 44

45 - 60

61 or more

How can I achieve this, please any help appreciated.

[luismadriz]

Hi,

instead of using just '0', use Dual('0',1)

instead of using '1', use Dual('1',2)

instead of using '2 - 5', use Dual('2 -5',3)

instead of using '6 - 29', use Dual('6 - 29',4)

and so on,

I hope this helps,

Cheers,

Luis

[gopal5879]

Luis, It works. Thanks You very much

[luismadriz · Accepted solution]

No problem,

When you can please close the thread by marking replies correct/helpful

Cheers,

Luis

[luismadriz]

not your  reply, my reply as helpful/correct,

Cheers,

Luis

[gopal5879]

Thank Luiz, I have marked them helpful

[evan_kurowski]

You could always use a bucketing system, which helps repeat the process.   If like I have at times, sometimes find you have to apply a multitude of buckets & segment brackets to a fact table simultaneously.  Might help reduce some of the very long expression syntaxes.

The control table gets loaded with the bucketing definition, and then you indicate which field it should be attached to, and it will be joined into your fact table by the end of the script.

[gopal5879]

I have closed Luiz

Thanks & regards,

Gopal Madireddy

Software Engineer - Reporting Team

Gopal.Madireddy@MultiPlan.com

MultiPlan

115 Fifth Avenue

New York, NY 10003

Ph: 212-539-8043