false Result

[master_student]

Hello Guys,

I hope all is well.

so I have a table thant calculate the difference between two dates creation date ans last modified date according to a group of rules,

after calculting the delais de traitement measure, I would like to set the intervalls of these measure,

here is my table :

heres the expression of my Intervalle field :

=if(time(Business_Hrs_Without_Overtime / 24, 'hh:mm:ss')>='00:30:00' and time(Business_Hrs_Without_Overtime / 24, 'hh:mm:ss')<'01:00:00',' >=30 Min',

if(time(Business_Hrs_Without_Overtime / 24, 'hh:mm:ss')<'00:30:00',' <30 Min'

,if(time(Business_Hrs_Without_Overtime / 24, 'hh:mm:ss')>='01:00:00' and time(Business_Hrs_Without_Overtime / 24, 'hh:mm:ss')<'02:00:00',' >=1h',

if(time(Business_Hrs_Without_Overtime / 24, 'hh:mm:ss')>='02:00:00' and time(Business_Hrs_Without_Overtime / 24, 'hh:mm:ss')<'04:00:00',' >=2h and <4h',

if(time(Business_Hrs_Without_Overtime / 24, 'hh:mm:ss')>='04:00:00'and time(Business_Hrs_Without_Overtime / 24, 'hh:mm:ss')<'05:00:00',' >4h and <5h',

if(time(Business_Hrs_Without_Overtime / 24, 'hh:mm:ss')>='05:00:00' and time(Business_Hrs_Without_Overtime / 24, 'hh:mm:ss')<'06:00:00',' >5h and <6h'

,if(time(Business_Hrs_Without_Overtime / 24, 'hh:mm:ss')>='09:00:00'and time(Business_Hrs_Without_Overtime / 24, 'hh:mm:ss')<'18:00:00','>=1j and <2j'

,if(time(Business_Hrs_Without_Overtime / 24, 'hh:mm:ss')>='18:00:00'and time(Business_Hrs_Without_Overtime / 24, 'hh:mm:ss')<'27:00:00','>=2j and <3j'

))))))

))

The problem is this formula gives me result like this '-'

Any idea how to correcte my formula ?

Thanks a lot

[YoussefBelloum]

Try to add sample data with the same structure

[stabben23]

If you look at the difference between Row_added_dttm and row_lastmant_dttm, it a lot more that 27 hour that is Your top value.

All other rows have the same date, just a Clue.

[master_student]

Any help pleaase

[Or]

Staffan has already answered your question above. If you think the response is not correct, you'll have to explain why.

[stabben23]

You could try to add an else statment, '-' mean nomatch. Add test after last if statment.

=if(time(Business_Hrs_Without_Overtime / 24, 'hh:mm:ss')>='00:30:00' and time(Business_Hrs_Without_Overtime / 24, 'hh:mm:ss')<'01:00:00',' >=30 Min',

if(time(Business_Hrs_Without_Overtime / 24, 'hh:mm:ss')<'00:30:00',' <30 Min'

,if(time(Business_Hrs_Without_Overtime / 24, 'hh:mm:ss')>='01:00:00' and time(Business_Hrs_Without_Overtime / 24, 'hh:mm:ss')<'02:00:00',' >=1h',

if(time(Business_Hrs_Without_Overtime / 24, 'hh:mm:ss')>='02:00:00' and time(Business_Hrs_Without_Overtime / 24, 'hh:mm:ss')<'04:00:00',' >=2h and <4h',

if(time(Business_Hrs_Without_Overtime / 24, 'hh:mm:ss')>='04:00:00'and time(Business_Hrs_Without_Overtime / 24, 'hh:mm:ss')<'05:00:00',' >4h and <5h',

if(time(Business_Hrs_Without_Overtime / 24, 'hh:mm:ss')>='05:00:00' and time(Business_Hrs_Without_Overtime / 24, 'hh:mm:ss')<'06:00:00',' >5h and <6h'

,if(time(Business_Hrs_Without_Overtime / 24, 'hh:mm:ss')>='09:00:00'and time(Business_Hrs_Without_Overtime / 24, 'hh:mm:ss')<'18:00:00','>=1j and <2j'

,if(time(Business_Hrs_Without_Overtime / 24, 'hh:mm:ss')>='18:00:00'and time(Business_Hrs_Without_Overtime / 24, 'hh:mm:ss')<'27:00:00','>=2j and <3j','test'

))))))

))

[stevedark]

Hi Wiem,

The most likely thing here is that you need another item on your IF for what if it doesn't match any of the timeframes.  As stabben23‌ has pointed out the dates are further apart on the last row.

If that is the problem then you can fix it by adding to the final line of code, for instance.:

,'>=2j and <3j', '>= 3j'

It doesn't look right that there is no aggregation in your expression, but this probably doesn't matter as you have both start and end times as dimensions (otherwise you would want Max and Min statements around each Business_Hrs_Without_Overtime).

The fact that you are comparing strings rather than numerics feels wrong, but as it is a fixed length string with zero padding this should work fine.  Instinctively though I would not use the time function and have the hour compare doing something like this for two hours:

((1 / 24) * 2)

This would also be more efficient - but what you have seems to work for the first set of dates so you could leave it as is.

What granularity are the overtimes stored to.  There is a possibility that something could fall between the gaps where times are rounded to the nearest second.

I would expect to see the if be nested from the shortest up, as you can then write less code;

if(<= 0.5,

     if(< 1,

     if(< 2,

     if(<24

     'Over a Day'))))

As it is you are checking for > and < on each check.

Hope that helps.

Steve

[stabben23]

if Your Expression "Intervalle" Depends on "Delais de Traitement" the you could use that instead of a New calculation.

like this instead:

if( [Delais de Traitement]>='00:30:00' and [Delais de Traitement]<'01:00:00',' >=30 Min',...aso