If Condition not working in Expression

[sivakumar1994]

Hi 

The below expression is not working as expected because the condition inisde if statement is never returning true value in expression of a chart, but the same condition is working fine when it was applied in a dimension. kindly assist

Expression: =if(Time(Timestamp#( Date(num(%ShiftDate)+1,'DD/MM/YYYY') &' '&EndTime,'DD/MM/YYYY hh.mm TT')) > Time(outtime),
[Late Time 2],'0')

--> [Late Time 2] = Time(Only({<%ShiftDate={'>=$(=Date(vStart))<=$(=Date(vEnd))'},MonthYear>}
(Timestamp#(Date(num(%ShiftDate)-1,)&' '&EndTime,'DD/MM/YYYY hh.mm TT')-outtime)),'hh:mm')

[sunny_talwar]

Would you be able to share a sample so that we can see the issue?

[uacg0009]

Hi,

Firstly I want to say that if you only need to compare the "time", it seems like that the "%ShiftDate" is useless.

And what's your dimension in this table, and your data model also affect the result.

It's better to share a small sample, so we can find the real reason.

Thanks.

Aiolos Zhao

[sivakumar1994]

Hi All,

Since data is confidential i wont be able to share the data, The dimension includes %shiftdate (DD/MM/YYYY) and shift start date (Example: 10 AM) , shift end time (4 PM).

Thanks

Siva

[uacg0009]

so am i right?
what you want to compare is time(EndTime) and time(outtime), right?
if yes, please use below expression firstly,
=if(Time(EndTime) > Time(outtime),1,0)
if you can get "1", then try to change "late time 2"
Aiolos Zhao

[sivakumar1994]

Partially correct.

There are possible scenario where shift end time is 2 am or 3 am (Next day time stamp) , hence =if(Time(EndTime) > Time(outtime),1,0) doesn't meet the expectation. I tried with below code in expression to identify late night shift time , but that doesn't work expected.

FYI, end time value is like '2:00 AM' whereas outime is swipe out timestamp value ('05-01-2018 01:06 AM') , so i am trying to add shiftdate ('04-01-2018') + 1 for shift end time containing '*Am*'.

if( Wildcard(EndTime,'*AM*'),

if(Time(Timestamp#( Date(num(%ShiftDate)+1,'DD/MM/YYYY') &' '&EndTime,'DD/MM/YYYY hh.mm TT')) > Time(outtime),
[Late Time 2],'0'),0)

--> [Late Time 2] = Time(Only({<%ShiftDate={'>=$(=Date(vStart))<=$(=Date(vEnd))'},MonthYear>}
(Timestamp#(Date(num(%ShiftDate)-1,)&' '&EndTime,'DD/MM/YYYY hh.mm TT')-outtime)),'hh:mm')

The above logic is not working. Kindly assist.

[uacg0009 · Accepted solution]

Hi,
I think if you want to compare these 2 "date", then you can not use the time() function, it will be wrong.
You need to compare it directly, like:
num(%ShiftDate) + 1 + EndTime > outtime
But there is a problem you need to test, because I don't have sample data, so I can't try.
You can select one column data, then you need to make sure that
num(%ShiftDate) + 1 + EndTime can get a value, but not null
I think num(%ShiftDate) + 1 is ok, the problem is when you + EndTime, I'm not sure it can get value or not. if not you need to change your EndTime to num, then add, I think you will get the correct result.
Please try.
Aiolos Zhao