Using calculated dimension order in sort (expression)

[zagzebski]

I created the following calculated dimension (which works well) to group buckets of Revenue:

If(Aggr(Sum([Amount Revenue Est BoB]),[Customer Name],[Producer])<5000,'Less Than 5,000',
If(Aggr(Sum([Amount Revenue Est BoB]),[Customer Name],[Producer])<10001,'5,000-10,000',
If(Aggr(Sum([Amount Revenue Est BoB]),[Customer Name],[Producer])<25001,'10,000-25,000',
If(Aggr(Sum([Amount Revenue Est BoB]),[Customer Name],[Producer])<50001,'25,000-50,000',
If(Aggr(Sum([Amount Revenue Est BoB]),[Customer Name],[Producer])<100001,'50,000-100,000',
If(Aggr(Sum([Amount Revenue Est BoB]),[Customer Name],[Producer])>100000,'Greater than 100,000','NA'
))))))

But I want the the sort order the same order as it appears in the expression above. The problem it is sorting like this (with the 5,000-10,000 out of order)

Less Than 5,000
10,000-25,000
25,000-50,000

5,000-10,000
50,000-100,000
Greater than 100,000'

[sunny_talwar]

Try this:

If(Aggr(Sum([Amount Revenue Est BoB]),[Customer Name],[Producer])<5000, Dual('Less Than 5,000', 1),
If(Aggr(Sum([Amount Revenue Est BoB]),[Customer Name],[Producer])<10001, Dual('5,000-10,000', 2),
If(Aggr(Sum([Amount Revenue Est BoB]),[Customer Name],[Producer])<25001, Dual('10,000-25,000', 3),
If(Aggr(Sum([Amount Revenue Est BoB]),[Customer Name],[Producer])<50001, Dual('25,000-50,000', 4),
If(Aggr(Sum([Amount Revenue Est BoB]),[Customer Name],[Producer])<100001, Dual('50,000-100,000', 5),
If(Aggr(Sum([Amount Revenue Est BoB]),[Customer Name],[Producer])>100000, Dual('Greater than 100,000', 6),'NA'
))))))

[zagzebski]

Thanks for the reply.

So I put this in my calculated dimension. It didn't really change anything, Is there something I should do from there?

[sunny_talwar · Accepted solution]

Yes use the numeric value sort ascending order and uncheck all others

[buzzy996]

hi

are u tried Sunny expression?

if not,then u can place ur above syntax in load statement and use that statement label as ur dimension that will gives ur expected out.

else share sample,we will quickly respond.

[zagzebski]

Perfect - thanks so much!

[sunny_talwar]

No problem