Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Custom Sorting based on Expression in Bar Chart

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.

2 Replies
bill_markham
Not applicable

Re: Custom Sorting based on Expression in Bar Chart

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
Not applicable

Re: Custom Sorting based on Expression in Bar Chart

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