Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

mhmmd_srf
Contributor 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

Re: Calculation on Date

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]

7 Replies

Re: Calculation on Date

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

Highlighted
mhmmd_srf
Contributor II

Re: Calculation on Date

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

Re: Calculation on Date

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
Valued Contributor

Re: Calculation on Date

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
Contributor II

Re: Calculation on Date

No Sunny it was my mistake. I misunderstood.

Let me check it thoroughly.

mhmmd_srf
Contributor II

Re: Calculation on Date

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

MVP
MVP

Re: Calculation on Date

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]