topic Re: Using calculated dimension order in sort (expression) in QlikView

Using calculated dimension order in sort (expression)

zagzebski — Fri, 13 Nov 2015 14:10:02 GMT

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'

Re: Using calculated dimension order in sort (expression)

sunny_talwar — Fri, 13 Nov 2015 14:14:10 GMT

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'
))))))

Re: Using calculated dimension order in sort (expression)

zagzebski — Fri, 13 Nov 2015 14:33:00 GMT

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?

Re: Using calculated dimension order in sort (expression)

sunny_talwar — Fri, 13 Nov 2015 14:34:55 GMT

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

Re: Using calculated dimension order in sort (expression)

buzzy996 — Fri, 13 Nov 2015 14:54:30 GMT

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.

Re: Using calculated dimension order in sort (expression)

zagzebski — Fri, 13 Nov 2015 15:11:52 GMT

Perfect - thanks so much!

Re: Using calculated dimension order in sort (expression)

sunny_talwar — Fri, 13 Nov 2015 15:12:19 GMT

No problem