Compare a time obtained from Min to one created with MakeTime()

[alespooletto]

I want to calculate if a time I obtain from the production of certain objects is before or after 15:45:00 of each day I am taking into consideration.

So, my idea was to do like this:

Time(Min({<ACTION_CODE={'LABOR_ON'}>} DateTime)) - MakeTime(15,45,00)

But when I compare these two, it gives me a very big number, and it seems like the time I create with MakeTime is associated with a very early date instead of just being the time only. How can I deal with this? I just want to understand if an event has happened before this datetime. 

[Anil_Babu_Samineni]

@alespooletto Maybe format like

Time(Min({<ACTION_CODE={'LABOR_ON'}>} DateTime)) - Time(MakeTime(15,45,00))

[alespooletto]

Thank you @Anil_Babu_Samineni , unfortunately I had tried with this but I still get a very large number that isn't what I am looking for.

It come out like this

[Rohan]

Hi @alespooletto ,

Try this instead :

num(Min({<ACTION_CODE={'LABOR_ON'}>} DateTime))-(num(floor(DateTime))+0.65625);

PS : 0.65625 is the numeric value for 15:45:00. I believe this will resolve your issue. Revert if any issues.

Regards,

Rohan.

[Anil_Babu_Samineni]

@alespooletto If you do one more Time format on top of like this?

Time(Time(Min({<ACTION_CODE={'LABOR_ON'}>} DateTime)) - Time(MakeTime(15,45,00)))

[alespooletto]

Thank you both @Anil_Babu_Samineni  @Rohan  for your suggestions. It is still an issue, because when I compare the two datetimes I don't reach the desired result. I will show you here a few things I worked on :

The first 2 columns are the start and end date of a specific task from a machine. What I am looking for is a measure that, when the task is started before 15:45 and ended after 16:00 on any given date, it will remove the 15 minutes duration. In the rightmost column, the measures I have tried with both of your suggestions don't give me the exact result, whereas in the "Overall Measure" column, I have succesfully did one row, but all the others are affected as well. You may check this measure here:

Interval(
    Max({<ACTION_CODE={'LABOR_OFF'}>} DateTime) -
    Min({<ACTION_CODE={'LABOR_ON'}>} DateTime) -
    If(
        // Break period 10:00 to 10:15
        (
            (Hour(Min({<ACTION_CODE={'LABOR_ON'}>} DateTime)) = 10 AND Minute(Min({<ACTION_CODE={'LABOR_ON'}>} DateTime)) < 0) OR
            (Hour(Max({<ACTION_CODE={'LABOR_OFF'}>} DateTime)) = 10 AND Minute(Max({<ACTION_CODE={'LABOR_OFF'}>} DateTime)) > 15)
        ) OR
        // Break period 15:45 to 16:00
        (
            (Hour(Min({<ACTION_CODE={'LABOR_ON'}>} DateTime)) = 15 AND Minute(Min({<ACTION_CODE={'LABOR_ON'}>} DateTime)) < 45) OR
            (Hour(Max({<ACTION_CODE={'LABOR_OFF'}>} DateTime)) = 16 AND Minute(Max({<ACTION_CODE={'LABOR_OFF'}>} DateTime)) > 0)
        ),
        // Subtract 15 minutes
        Interval(0.0104167),
        // ELSE IF Break period 12:30 - 13:30
        If(
            (
                (Hour(Min({<ACTION_CODE={'LABOR_ON'}>} DateTime)) = 12 AND Minute(Min({<ACTION_CODE={'LABOR_ON'}>} DateTime)) < 30) AND
                (Hour(Max({<ACTION_CODE={'LABOR_OFF'}>} DateTime)) = 13 AND Minute(Max({<ACTION_CODE={'LABOR_OFF'}>} DateTime)) >= 30)
            ),
            // Subtract 1 hour
            Interval(0.0416667),
            // ELSE
            0 // No adjustment
        )
    ),
    'hh:mm:ss'
)

Please do let me know if you spot any issues with the measure, such as it will exit the conditions I want to give it. The conditions are:

IF datetime of start before 10:00 or before 12:30 or before 15:45

AND datetime of end after 10:15 or after 13:30 or after 16:00 

THEN remove the time in between of those for the total duration when subtracting them.

The objective is to get a final duration of the product time that takes into consideration when the machine is stopped being operated on. Thank you!

[Anil_Babu_Samineni · Accepted solution]

@alespooletto Condition look ok for me, Is there any error when you validate?

And, The same screenshot you sent please do like this

Expression 1: Time(Min({<ACTION_CODE={'LABOR_ON'}>} DateTime))

Expression 2: Time(MakeTime(15,45,00))

Final: Time(Time(Min({<ACTION_CODE={'LABOR_ON'}>} DateTime)) - Time(MakeTime(15,45,00)))

Send us screenshot again to see the rows that what is difference. 

[alespooletto]

Thank you @Anil_Babu_Samineni 

Unfortunately the conditions albeit they seem fine but they seem to always subtract the 15 minutes when the time is either 15 or 16. Not just when necessary. You can see an example here, with the 3 columns you asked:

The products before 15 are okay, not presenting the subtraction with 15 minutes. For the step started at 15:43, and ended at 16:07 this is also okay, as it correctly subtracts the 15 minutes. But the others get subtracted without it being necessary.

I have also started wondering if this is related to the default times that I have pre-fixed in the app. Thanks for your help.

[Anil_Babu_Samineni]

@alespooletto With this, It is really difficult to help because I don't have any data to test with.

If you add before 10 AM, It never understand to Qlik on which date 🙂 So, For that, I have created a flag long back to determine that what duration is needed. 

Assume, If(Time(10)>10 and Flag='Morning', ......)

Here the Flag is calculated from If(Time(10)>00 and Time(10)<10, 'Morning'), ...

With this, The condition can understand on the full 24 hours with AM and PM to determined due to AND operator (I assume). 

[alespooletto]

I'm very sorry @Anil_Babu_Samineni , I'm not understanding well. Do you mean to place a flag and then use binary conditions with that? If you want some data to test with, I can give you the extraction of that table, the data is all masked anyway.