Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
why don't u calculate it in script and use that fieldname in If statement.