How to calculate a time interval for the day before a selected date?

[dan205]

My Qlik app loads two datetime fields ('YYYY-MM-DD hh:mm:ss.fff'), which are the start and end datetimes for some daily scheduled processes:

[Start DateTime]

[End DateTime]

Each date has multiple processes (rows) with a start and end time. 

I would like to show, in a Multi-KPI, the total duration of all processes for a selected date, and also for the day before the selected date.

I can use the following expression to create a filter for the date (so I only get one per date and not one per row):

Date(Floor([Start DateTime]))

I can then calculate the total duration for a selected date by isolating the min start time and max end time, and subtracting with an Interval() function. 

Interval( Max(Time(Frac([End DateTime]), 'hh:mm:ss')) - Min(Time(Frac([Start DateTime]), 'hh:mm:ss')) )

But I can't work out how to do this for the selected date minus one day. Am I approaching this the wrong way? Can anyone help?

Thanks in advance,

Dan

[Oleg_Troyansky]

Hi Dan,

There are several issues to consider here. The formula that you are using, will give you the interval between the highest end time of all processes and the lowest start time of all processes - is that what you are looking for? 

If you wanted to aggregate durations of individual processes, then you should first subtract them and then summarize the results of the subtraction:

sum(Endtime - Starttime)

Are your processes short enough to always start and finish in the same day? If so, you don't need to worry about using the Frac function - the date component of the timestamp is going to be "a wash".

However, if your processes might cross multiple days, then you definitely should NOT use the Frac function, because you will get wrong results. In this case, you need to decide what processes belong to a particular day - those that started or those that finished on the same day. For example, if you let the users select the Start Date, then the Set Analysis filter would look like this:

{< [Start Date]= {'$(=date(max(Floor([Start Date]))-1))'}   >}

To learn more advanced development techniques, including Advanced Set Analysis, check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!

Finally, to your question about the previous day. You'd need to use Set Analysis that would select the day before, replacing the user selection in the field Date

[dan205]

Hi Oleg, thanks for replying.

Yes, initially I'm looking for the total duration of all processes. The processes run every day and always end before the next day.

Dan