Calculation on Date

[mhmmd_srf]

Hi All,

I have a date filed called Breach Time in MM/DD/YYYY hh:mm:ss format.

Now I want below logic to be applied.

It will check if it is crossing 17:00:00 or not, if crossing then Qlik will check the difference and the difference will be added from 08:00:00 AM.

Example:
Case 1: 10-04-2018  15:34:32 --- will show same  10-04-2018  15:34:32.

Case 2: 10-04-2018  19:34:32 --- Will show           10-05-2018  10:34:32.

Can anyone help me to do this.

Thanks,

Sarif

[sunny_talwar]

May be this

TimeStamp(If(Hour([Breach Time) > 17, [Breach Time] + MakeTime(15), [Breach Time])) as [Breach Time]

For example:

=TimeStamp(If(Hour(MakeDate(2018, 10, 4) + MakeTime(19, 34, 32)) > 17,

MakeDate(2018, 10, 4) + MakeTime(19, 34, 32) + MakeTime(15), MakeDate(2018, 10, 4) + MakeTime(19, 34, 32)))

[mhmmd_srf]

Hi Sunny,

Above expression is throwing error saying hour takes 1 parameter.

and second thing putting 15 is right approach here?

TimeStamp(If(Hour([Breach Time) > 17, [Breach Time] + MakeTime(15), [Breach Time])) as [Breach Time]

Please suggest.

Thanks,

Sarif

[sunny_talwar · Accepted solution]

What is wrong if you get to the right result? I mean do you have a scenario where it doesn't match... from what I understood... if the time is 17:01 PM... you want to make it 8:01 AM the next day, right? 7 Hour from today and 8 hours from tomorrow make it 15 hours... what is wrong about this approach? May be I am missing something, but feel free to correct me.

I missed a square bracket after Breach Time within Hour... try this

TimeStamp(If(Hour([Breach Time]) > 17, [Breach Time] + MakeTime(15), [Breach Time])) as [Breach Time]

[passionate]

working with date or timestamp the best technique is to convert in Numeric format do you calsulations and then convert numeric back to timestamp.

[mhmmd_srf]

No Sunny it was my mistake. I misunderstood.

Let me check it thoroughly.

[mhmmd_srf]

Hello Sunny,

It is not working always. I am attaching my test file. Please check for INC0972080.

Please check Bus_Breach_New and BreachTime_New1.

Thanks,

Sarif

[tresesco]

You have to consider the minutes, seconds as well. Try like:

Date(TimeStamp(If(frac(Bus_Breach_New)>17/24,  [Bus_Breach_New] + MakeTime(15), [Bus_Breach_New])),'MM/DD/YYYY hh:mm:ss') as [Breach Time]