topic Dynamic Calculation of Event Duration Depending on Selected Time Range in App Development

Dynamic Calculation of Event Duration Depending on Selected Time Range

Maike151 — Wed, 16 Nov 2022 09:36:20 GMT

Dear Qlik Community,

I would like to calculate the duration of an event even when a time range is selected that is smaller than the event itself.

Here is a simplified example:

I have a table with machine events kind of like this:

Events:

EventID	MachineID	Status	Start	End
1	100	Production	01.11.2022 15:00:00	12.11.2022 12:00:00
2	100	Downtime	12.11.2022 12:00:00	12.11.2022 12:30:00
3	100	Production	12.11.2022 12:30:00	16.11.2022 10:00:00

(Date format is DD.MM.YYYY hh.mm.ss)

My measures then are like

Event duration (minutes): Sum(End-Start)*24*60
Production time: Sum({$<Status={'Production'}>} End-Start)*24*60
Downtime: Sum({$<Status={'Downtime'}>} End-Start)*24*60

Everything is fine until here.

Now I want to know for each day, week, month, year,... how long the machine produced and how long it was in Downtime. But when I use Start as a filter dimension and I filter e.g. the 02.11.2022, production and downtime is obviously zero because there is no Start on 02.11.2022. What I want to see is Production time = 24 h.

If I filter 12.11.2022 I would like to see Production time = 23,5 h, Downtime = 0,5 h.

So Start does not seem to be the right dimension to filter by, but what else could I use?

I already tried a solution that works but is very inefficient. I stored every single minute an event is active in a seperate table like this:

Event_Duration: LOAD Timestamp(Start + ((IterNo()-1)/(24*60))) as Active_Timestamp, EventID Resident Events While Start + ((IterNo()-1)/(24*60)) <= End;

Then in the App I use Active_Timestamp as filter dimension and

Count(Active_Timestamp) as my measure Event duration (minutes).

It works fine but to store every single minute of every event of our more than 100 machines does not seem to be the right solution for me. I am getting time out errors with a limited data set already.

Does anyone have some ideas or inspiration for me? Thanks in advance!

Re: Dynamic Calculation of Event Duration Depending on Selected Time Range

vinieme12 — Thu, 17 Nov 2022 11:02:24 GMT

as below

temp: Load * ,date(Startdate+iterno()-1) as DailyDate ,if(Startdate=date(Startdate+iterno()-1) , interval(Time#('23:59','hh:mm')-Starttime,'hh') ,if(Enddate=date(Startdate+iterno()-1) , hour(Endtime) , 24) )as Hours while (Startdate+IterNo()-1)<=Enddate ; load EventID ,MachineID ,Status ,Timestamp(Timestamp#(Start,'DD.MM.YYYY hh:mm:ss')) as Starttimestamp ,timestamp(Timestamp#(End,'DD.MM.YYYY hh:mm:ss')) as Endtimestamp ,date(floor(Timestamp#(Start,'DD.MM.YYYY hh:mm:ss'))) as Startdate ,date(floor(Timestamp#(End,'DD.MM.YYYY hh:mm:ss'))) as Enddate ,time(timestamp(Frac(Timestamp#(Start,'DD.MM.YYYY hh:mm:ss')))) as Starttime ,time(timestamp(frac(Timestamp#(End,'DD.MM.YYYY hh:mm:ss')))) as Endtime inline [ EventID,MachineID,Status,Start,End 1,100,Production,01.11.2022 15:00:00,12.11.2022 12:00:00 2,100,Downtime,12.11.2022 12:00:00,12.11.2022 12:30:00 3,100,Production,12.11.2022 12:30:00,16.11.2022 10:00:00 ];

Just use sum(Hours) in chart and create calendar linked to DailyDate field

Re: Dynamic Calculation of Event Duration Depending on Selected Time Range

Maike151 — Thu, 08 Dec 2022 14:20:10 GMT

Thank you very much!!