Changing a dimension to a field to sort with match function

[AdrianS1]

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 ?

[Oleg_Troyansky]

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,