Dynamic Calculation of Event Duration Depending on Selected Time Range

[Maike151]

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!

[vinieme12 · Accepted solution]

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

[Maike151]

Thank you very much!!