Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, guys I want to sort my expression :
=if(floor(aggr(
if(({<STATUS={$(vStatus2)}>} FLAG_WEEKEND ='1') AND ({<STATUS={$(vStatus1)}>} FLAG_WEEKEND ='0'),
interval(interval(NetWorkDays(({<STATUS={$(vStatus1)}>} correct_status_date_timestamp), ({<STATUS={$(vStatus2)}>} correct_status_date_timestamp),$(vAllHolidays)) ,'hh:mm')
-interval(frac(({<STATUS={$(vStatus1)}>} correct_status_date_timestamp)),'hh:mm'),'hh:mm')
,if(({<STATUS={$(vStatus2)}>} FLAG_WEEKEND ='0') AND ({<STATUS={$(vStatus1)}>} FLAG_WEEKEND ='1'),
interval(interval(NetWorkDays(({<STATUS={$(vStatus1)}>} correct_status_date_timestamp), ({<STATUS={$(vStatus2)}>} correct_status_date_timestamp),$(vAllHolidays)) ,'hh:mm')
-interval(1-frac({<STATUS={$(vStatus2)}>} correct_status_date_timestamp),'hh:mm'),'hh:mm')
,if(({<STATUS={$(vStatus2)}>} FLAG_WEEKEND ='1') AND ({<STATUS={$(vStatus1)}>} FLAG_WEEKEND ='1'),
interval(NetWorkDays(({<STATUS={$(vStatus1)}>} correct_status_date_timestamp), ({<STATUS={$(vStatus2)}>} correct_status_date_timestamp),$(vAllHolidays)) ,'hh:mm')
,if(({<STATUS={$(vStatus2)}>} FLAG_WEEKEND ='0') AND ({<STATUS={$(vStatus1)}>} FLAG_WEEKEND ='0'),
interval(interval(NetWorkDays(({<STATUS={$(vStatus1)}>} correct_status_date_timestamp), ({<STATUS={$(vStatus2)}>} correct_status_date_timestamp),$(vAllHolidays)) ,'hh:mm')
-interval(frac(({<STATUS={$(vStatus1)}>} correct_status_date_timestamp)),'hh:mm')
-interval(1-frac({<STATUS={$(vStatus2)}>} correct_status_date_timestamp),'hh:mm'),'hh:mm')))))
,STATUS),'24:00') = 0,'0 days',
if(floor(aggr(
if(({<STATUS={$(vStatus2)}>} FLAG_WEEKEND ='1') AND ({<STATUS={$(vStatus1)}>} FLAG_WEEKEND ='0'),
interval(interval(NetWorkDays(({<STATUS={$(vStatus1)}>} correct_status_date_timestamp), ({<STATUS={$(vStatus2)}>} correct_status_date_timestamp),$(vAllHolidays)) ,'hh:mm')
-interval(frac(({<STATUS={$(vStatus1)}>} correct_status_date_timestamp)),'hh:mm'),'hh:mm')
,if(({<STATUS={$(vStatus2)}>} FLAG_WEEKEND ='0') AND ({<STATUS={$(vStatus1)}>} FLAG_WEEKEND ='1'),
interval(interval(NetWorkDays(({<STATUS={$(vStatus1)}>} correct_status_date_timestamp), ({<STATUS={$(vStatus2)}>} correct_status_date_timestamp),$(vAllHolidays)) ,'hh:mm')
-interval(1-frac({<STATUS={$(vStatus2)}>} correct_status_date_timestamp),'hh:mm'),'hh:mm')
,if(({<STATUS={$(vStatus2)}>} FLAG_WEEKEND ='1') AND ({<STATUS={$(vStatus1)}>} FLAG_WEEKEND ='1'),
interval(NetWorkDays(({<STATUS={$(vStatus1)}>} correct_status_date_timestamp), ({<STATUS={$(vStatus2)}>} correct_status_date_timestamp),$(vAllHolidays)) ,'hh:mm')
,if(({<STATUS={$(vStatus2)}>} FLAG_WEEKEND ='0') AND ({<STATUS={$(vStatus1)}>} FLAG_WEEKEND ='0'),
interval(interval(NetWorkDays(({<STATUS={$(vStatus1)}>} correct_status_date_timestamp), ({<STATUS={$(vStatus2)}>} correct_status_date_timestamp),$(vAllHolidays)) ,'hh:mm')
-interval(frac(({<STATUS={$(vStatus1)}>} correct_status_date_timestamp)),'hh:mm')
-interval(1-frac({<STATUS={$(vStatus2)}>} correct_status_date_timestamp),'hh:mm'),'hh:mm')))))
,STATUS),'24:00') =1,'1 days',
if(floor(aggr(
if(({<STATUS={$(vStatus2)}>} FLAG_WEEKEND ='1') AND ({<STATUS={$(vStatus1)}>} FLAG_WEEKEND ='0'),
interval(interval(NetWorkDays(({<STATUS={$(vStatus1)}>} correct_status_date_timestamp), ({<STATUS={$(vStatus2)}>} correct_status_date_timestamp),$(vAllHolidays)) ,'hh:mm')
-interval(frac(({<STATUS={$(vStatus1)}>} correct_status_date_timestamp)),'hh:mm'),'hh:mm')
,if(({<STATUS={$(vStatus2)}>} FLAG_WEEKEND ='0') AND ({<STATUS={$(vStatus1)}>} FLAG_WEEKEND ='1'),
interval(interval(NetWorkDays(({<STATUS={$(vStatus1)}>} correct_status_date_timestamp), ({<STATUS={$(vStatus2)}>} correct_status_date_timestamp),$(vAllHolidays)) ,'hh:mm')
-interval(1-frac({<STATUS={$(vStatus2)}>} correct_status_date_timestamp),'hh:mm'),'hh:mm')
,if(({<STATUS={$(vStatus2)}>} FLAG_WEEKEND ='1') AND ({<STATUS={$(vStatus1)}>} FLAG_WEEKEND ='1'),
interval(NetWorkDays(({<STATUS={$(vStatus1)}>} correct_status_date_timestamp), ({<STATUS={$(vStatus2)}>} correct_status_date_timestamp),$(vAllHolidays)) ,'hh:mm')
,if(({<STATUS={$(vStatus2)}>} FLAG_WEEKEND ='0') AND ({<STATUS={$(vStatus1)}>} FLAG_WEEKEND ='0'),
interval(interval(NetWorkDays(({<STATUS={$(vStatus1)}>} correct_status_date_timestamp), ({<STATUS={$(vStatus2)}>} correct_status_date_timestamp),$(vAllHolidays)) ,'hh:mm')
-interval(frac(({<STATUS={$(vStatus1)}>} correct_status_date_timestamp)),'hh:mm')
-interval(1-frac({<STATUS={$(vStatus2)}>} correct_status_date_timestamp),'hh:mm'),'hh:mm')))))
,STATUS),'24:00') =2,'2 days',
if(floor(aggr(
if(({<STATUS={$(vStatus2)}>} FLAG_WEEKEND ='1') AND ({<STATUS={$(vStatus1)}>} FLAG_WEEKEND ='0'),
interval(interval(NetWorkDays(({<STATUS={$(vStatus1)}>} correct_status_date_timestamp), ({<STATUS={$(vStatus2)}>} correct_status_date_timestamp),$(vAllHolidays)) ,'hh:mm')
-interval(frac(({<STATUS={$(vStatus1)}>} correct_status_date_timestamp)),'hh:mm'),'hh:mm')
,if(({<STATUS={$(vStatus2)}>} FLAG_WEEKEND ='0') AND ({<STATUS={$(vStatus1)}>} FLAG_WEEKEND ='1'),
interval(interval(NetWorkDays(({<STATUS={$(vStatus1)}>} correct_status_date_timestamp), ({<STATUS={$(vStatus2)}>} correct_status_date_timestamp),$(vAllHolidays)) ,'hh:mm')
-interval(1-frac({<STATUS={$(vStatus2)}>} correct_status_date_timestamp),'hh:mm'),'hh:mm')
,if(({<STATUS={$(vStatus2)}>} FLAG_WEEKEND ='1') AND ({<STATUS={$(vStatus1)}>} FLAG_WEEKEND ='1'),
interval(NetWorkDays(({<STATUS={$(vStatus1)}>} correct_status_date_timestamp), ({<STATUS={$(vStatus2)}>} correct_status_date_timestamp),$(vAllHolidays)) ,'hh:mm')
,if(({<STATUS={$(vStatus2)}>} FLAG_WEEKEND ='0') AND ({<STATUS={$(vStatus1)}>} FLAG_WEEKEND ='0'),
interval(interval(NetWorkDays(({<STATUS={$(vStatus1)}>} correct_status_date_timestamp), ({<STATUS={$(vStatus2)}>} correct_status_date_timestamp),$(vAllHolidays)) ,'hh:mm')
-interval(frac(({<STATUS={$(vStatus1)}>} correct_status_date_timestamp)),'hh:mm')
-interval(1-frac({<STATUS={$(vStatus2)}>} correct_status_date_timestamp),'hh:mm'),'hh:mm')))))
,STATUS),'24:00') >=3,'> 3 days'
))))
by days like '0 days'-> '1 days'->'2 days'->'3 days'.
I want to use Match and Dual function to sort this data.
How can I put into load editor this dimension above to have a field ?
Or maybe you know other solutions to resolve this problem ?
Hi Adrian,
This calculation appears to be awfully complex. Without even attempting to understand what all of it means, I'd say that I'd look for a way of calculating this in the data load script and not in a chart dimension. Calculating it on the fly must create an enormous overhead and latency.
Cheers,