Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mhmmd_srf
Creator II
Creator II

Calculation on Date

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

1 Solution

Accepted Solutions
sunny_talwar

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]

View solution in original post

7 Replies
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
Creator II
Creator II
Author

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

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
Specialist
Specialist

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
Creator II
Creator II
Author

No Sunny it was my mistake. I misunderstood.

Let me check it thoroughly.

mhmmd_srf
Creator II
Creator II
Author

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
MVP
MVP

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]