Custom Sorting based on Expression in Bar Chart

[#1]

Hello, Everyone.

I have an expression in a dimension for a bar chart which is:

IF(AGGR(INTERVAL(DATE(rangemin (alt_vor_end, aggr(max(date),bus_no)),'M/D/YYYY') - MAX({1<date = >} VOR_Start_Date),'D'),bus_no) > 0 AND AGGR(INTERVAL(DATE(rangemin (alt_vor_end, aggr(max(date),bus_no)),'M/D/YYYY') - MAX({1<date = >} VOR_Start_Date),'D'),bus_no) < 2, '1',

IF(AGGR(INTERVAL(DATE(rangemin (alt_vor_end, aggr(max(date),bus_no)),'M/D/YYYY') - MAX({1<date = >} VOR_Start_Date),'D'),bus_no) > 1 AND AGGR(INTERVAL(DATE(rangemin (alt_vor_end, aggr(max(date),bus_no)),'M/D/YYYY') - MAX({1<date = >} VOR_Start_Date),'D'),bus_no) < 6, '2-5',      

IF(AGGR(INTERVAL(DATE(rangemin (alt_vor_end, aggr(max(date),bus_no)),'M/D/YYYY') - MAX({1<date = >} VOR_Start_Date),'D'),bus_no) > 5 AND AGGR(INTERVAL(DATE(rangemin (alt_vor_end, aggr(max(date),bus_no)),'M/D/YYYY') - MAX({1<date = >} VOR_Start_Date),'D'),bus_no) < 11, '6-10',      

IF(AGGR(INTERVAL(DATE(rangemin (alt_vor_end, aggr(max(date),bus_no)),'M/D/YYYY') - MAX({1<date = >} VOR_Start_Date),'D'),bus_no) > 10 AND AGGR(INTERVAL(DATE(rangemin (alt_vor_end, aggr(max(date),bus_no)),'M/D/YYYY') - MAX({1<date = >} VOR_Start_Date),'D'),bus_no) < 16, '11-15',

IF(AGGR(INTERVAL(DATE(rangemin (alt_vor_end, aggr(max(date),bus_no)),'M/D/YYYY') - MAX({1<date = >} VOR_Start_Date),'D'),bus_no) > 15 AND AGGR(INTERVAL(DATE(rangemin (alt_vor_end, aggr(max(date),bus_no)),'M/D/YYYY') - MAX({1<date = >} VOR_Start_Date),'D'),bus_no) < 21, '16-20',

IF(AGGR(INTERVAL(DATE(rangemin (alt_vor_end, aggr(max(date),bus_no)),'M/D/YYYY') - MAX({1<date = >} VOR_Start_Date),'D'),bus_no) > 20 AND AGGR(INTERVAL(DATE(rangemin (alt_vor_end, aggr(max(date),bus_no)),'M/D/YYYY') - MAX({1<date = >} VOR_Start_Date),'D'),bus_no) < 26, '21-25',

IF(AGGR(INTERVAL(DATE(rangemin (alt_vor_end, aggr(max(date),bus_no)),'M/D/YYYY') - MAX({1<date = >} VOR_Start_Date),'D'),bus_no) > 25 AND AGGR(INTERVAL(DATE(rangemin (alt_vor_end, aggr(max(date),bus_no)),'M/D/YYYY') - MAX({1<date = >} VOR_Start_Date),'D'),bus_no) < 31, '26-30',

IF(AGGR(INTERVAL(DATE(rangemin (alt_vor_end, aggr(max(date),bus_no)),'M/D/YYYY') - MAX({1<date = >} VOR_Start_Date),'D'),bus_no) > 30, '>30'

   ))))))))

I have tried MATCH, but it is not working.

I tried to do the IF THEN ELSE, but in the expression it could only allow 64 rows of data.

[Anonymous]

Wow !!!  Thats is an impressive IT statement. 

Even if you get it working, you are likely to end up with performance issues with a complex calculated dimension like this.

I would approach it by creating an additional dimension in the load script.  Then your front end expressions will be a lot simpler.

Although maybe the Class() function could help in the front end.

https://help.qlik.com/en-US/sense/3.2/Subsystems/Hub/Content/Scripting/ConditionalFunctions/class.ht...

[shraddha_g]

why don't u calculate it in script and  use that fieldname in If statement.