Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gopal5879
Creator
Creator

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.

1 Solution

Accepted Solutions
luismadriz
Specialist
Specialist

No problem,

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

Cheers,

Luis

View solution in original post

7 Replies
luismadriz
Specialist
Specialist

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
Creator
Creator
Author

Luis, It works. Thanks You very much

luismadriz
Specialist
Specialist

No problem,

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

Cheers,

Luis

luismadriz
Specialist
Specialist

not your  reply, my reply as helpful/correct,

Cheers,

Luis

gopal5879
Creator
Creator
Author

Thank Luiz, I have marked them helpful

evan_kurowski
Specialist
Specialist

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.

dynamic_bucketing_from_table.png

gopal5879
Creator
Creator
Author

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