Create order list

[cottrera]

Hi 

I have a table with many rows of repairs our complany has completed, depending on the age of the job a age distribution is added. However the order of the age distribution is not correct. I need to add a new column with a multiple  if statement to say if ="0-4" , then 1, IF ="5-9", then 2, IF = "10-14", then 3    

and so on. I have shown the order I would like

Order	WorkingDaysToCompBuckets
1	0-4
2	5-9
3	10-14
4	15-19
5	20-24
6	25-29
7	30-34
8	35-39
9	40-44
10	45-49
11	50-54
12	55-59
13	60-64
14	65-69
15	70-74
16	75-79
17	>=80

Thank you

Richard

[Zubair_Muhammad · Accepted solution]

@cottrera 

Instead of multiple if statements try this approach.

See attached file as well

First extract the ending range value using

EndingRange =
VAR pos1 =
    SEARCH ( "-", [WorkingDaysToCompBuckets], 1, 0 )
VAR pos2 =
    IF ( pos1 = 0, SEARCH ( "=", [WorkingDaysToCompBuckets], 1, 0 ), pos1 )
RETURN
    VALUE (
        RIGHT ( [WorkingDaysToCompBuckets], LEN ( [WorkingDaysToCompBuckets] ) - pos2 )
    )

Then we can RANK this column

RANK = RANKX(Table1,[EndingRange],,ASC,Dense)

[cottrera]

Thank you this works fine

Richard