topic Calculation on Date in QlikView

Calculation on Date

mhmmd_srf — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Calculation on Date

sunny_talwar — Sat, 11 Aug 2018 22:56:35 GMT

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

Re: Calculation on Date

mhmmd_srf — Sun, 12 Aug 2018 05:20:40 GMT

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

sunny_talwar — Sun, 12 Aug 2018 13:37:22 GMT

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]

Re: Calculation on Date

passionate — Sun, 12 Aug 2018 14:12:59 GMT

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

Re: Calculation on Date

mhmmd_srf — Sun, 12 Aug 2018 15:40:46 GMT

No Sunny it was my mistake. I misunderstood.

Let me check it thoroughly.

Re: Calculation on Date

mhmmd_srf — Mon, 13 Aug 2018 10:30:42 GMT

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

Re: Calculation on Date

tresB — Mon, 13 Aug 2018 10:48:53 GMT

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]