7 Replies Latest reply: Jan 12, 2018 6:55 AM by Gopal Madireddy

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

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.

• ###### Re: Hey Guys Can some one help or give idea about sorting Age Bucket in Character

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

• ###### Re: Hey Guys Can some one help or give idea about sorting Age Bucket in Character

Luis, It works. Thanks You very much

• ###### Re: Hey Guys Can some one help or give idea about sorting Age Bucket in Character

No problem,

Cheers,

Luis

• ###### Re: Hey Guys Can some one help or give idea about sorting Age Bucket in Character

Cheers,

Luis

• ###### Re: Hey Guys Can some one help or give idea about sorting Age Bucket in Character

I have closed Luiz

Thanks & regards,

Software Engineer - Reporting Team

MultiPlan

115 Fifth Avenue

New York, NY  10003

Ph: 212-539-8043

• ###### Re: Hey Guys Can some one help or give idea about sorting Age Bucket in Character

Thank Luiz, I have marked them helpful

• ###### Re: Hey Guys Can some one help or give idea about sorting Age Bucket in Character

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.