Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

gopal5879
New Contributor III

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
Valued Contributor

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

No problem,

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

Cheers,

Luis

7 Replies
luismadriz
Valued Contributor

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

gopal5879
New Contributor III

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

Luis, It works. Thanks You very much

luismadriz
Valued Contributor

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

No problem,

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

Cheers,

Luis

luismadriz
Valued Contributor

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

not your  reply, my reply as helpful/correct,

Cheers,

Luis

gopal5879
New Contributor III

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

Thank Luiz, I have marked them helpful

evan_kurowski
Valued Contributor

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.

dynamic_bucketing_from_table.png

gopal5879
New Contributor III

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

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

Community Browser