topic Re: Compare a time obtained from Min to one created with MakeTime() in App Development

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

alespooletto — Thu, 11 Jan 2024 14:27:04 GMT

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.

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

Anil_Babu_Samineni — Thu, 11 Jan 2024 14:38:22 GMT

@alespooletto Maybe format like

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

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

alespooletto — Thu, 11 Jan 2024 15:08:58 GMT

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

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

Rohan — Fri, 12 Jan 2024 06:01:17 GMT

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.

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

Anil_Babu_Samineni — Fri, 12 Jan 2024 09:01:37 GMT

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

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

alespooletto — Fri, 12 Jan 2024 12:50:46 GMT

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!

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

Anil_Babu_Samineni — Fri, 12 Jan 2024 13:54:01 GMT

@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.

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

alespooletto — Fri, 12 Jan 2024 14:04:55 GMT

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.

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

Anil_Babu_Samineni — Fri, 12 Jan 2024 14:08:53 GMT

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

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

alespooletto — Fri, 12 Jan 2024 14:14:12 GMT

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.

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

Anil_Babu_Samineni — Fri, 12 Jan 2024 14:16:33 GMT

@alespooletto Sorry and Exactly, With Binary conditions combination you can be able to because like I said that data shows with Time and not full timestamp so Qlik not able to process the Time if that is with Date.

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

alespooletto — Mon, 15 Jan 2024 09:03:30 GMT

@Anil_Babu_Samineni Hey, sorry for the delay. I was able to work with this in a different, and actually easier way. But so far, I only have to work on adding a couple more details, such as removing 16 hours when the product was started at 17:29 for example and finished 8:31 the next day. Or 48+16 hours if it's started on Friday and finished Monday.

This is the current formula. I feel like adding new IFs at the end would be the straightforward choice. But I am curious if in QLIK there is a function like SWITCH in Power BI, where you can get multiple IF statements at once?

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) AND (Hour(Max({<ACTION_CODE={'LABOR_OFF'}>} DateTime)) >= 10 AND Minute(Min({<ACTION_CODE={'LABOR_ON'}>} 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) AND (Hour(Max({<ACTION_CODE={'LABOR_OFF'}>} DateTime)) >= 16 AND Minute(Min({<ACTION_CODE={'LABOR_ON'}>} 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' )

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

Anil_Babu_Samineni — Mon, 15 Jan 2024 09:44:18 GMT

@alespooletto SWITCH is there, but not in UX and it is designed only in Script So, I would recommend you move from UX to script level because such conditions will degrade the performance on object level, and it decreases the performance of whole dashboard.

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

alespooletto — Mon, 15 Jan 2024 10:25:03 GMT

Yes, I think it's better to move all at the script level. The only issue I have is when I tried before, I couldn't seem to use operations such as

Hour(Max({<ACTION_CODE={'LABOR_OFF'}>} DateTime))

Because it wouldn't properly read those {} graphs

Thank you so much again.