Calculating hourly coverage in UI

[EliGohar]

Hi!

I have a challenge in Qlik Sense that I want to implement in UI only and I'd like to ask if it is feasible.

Below is an example of the data I have:

objectid	start time	end time
1	14:00	14:10
2	14:05	14:23
3	14:30	14:45
4	14:45	15:00

My goal is to identify how much time the objects covered at the given hour.

According to the example below - The result should be 90% - That's because 14:24-14:29 are not covered by any objects (54 minutes were covered out of the hour.)

Is it possible to implement such a calculation using only UI?

[diegozecchini]

Hi!
It's not very clear for me what you mean only using UI, but if it is ok to set analysis and calculated dimensions/measures I think you can afford it.

Begin with ensuring that your start and end times are loaded in a time format that Qlik Sense recognizes (e.g., hh:mm or a timestamp).
Then use Qlik's IntervalMatch() or another suitable approach to create a dataset that maps each minute in the hour to the relevant objects. However, since you're constrained to the UI, you can approximate this using calculated fields.

You can determine the overlap of each object's start and end times with the hour being evaluated, and then sum up the covered minutes across all objects.
Example:

Sum(
Aggr(
RangeMin(60, IntervalEnd(EndTime, MakeTime(14,0,0) + 1/24)) -
RangeMax(0, IntervalStart(StartTime, MakeTime(14,0,0))),
ObjectID
)
)
Adjust the MakeTime(14,0,0) for each hour as needed.

Divide the total covered minutes by the total minutes in the hour (60).

Example:
Sum(
Aggr(
RangeMin(60, IntervalEnd(EndTime, MakeTime(14,0,0) + 1/24)) -
RangeMax(0, IntervalStart(StartTime, MakeTime(14,0,0))),
ObjectID
)
) / 60

Format the result as a percentage by multiplying by 100 or using the UI formatting options.

Visualization in Qlik Sense
Create a straight table or KPI object to display the result.
Use a calculated dimension for the hours (Hour(StartTime)).
Use the calculated coverage percentage as a measure.

Given your data:

Object 1: Covers 10 minutes in hour 14.
Object 2: Covers 18 minutes in hour 14.
Object 3: Covers 15 minutes in hour 14.
Object 4: Covers 15 minutes in hour 14.
Sum of covered minutes = 10 + 18 + 15 + 15 = 58 minutes.

Percentage coverage = (58 / 60) * 100 = 90%

Does it works for you?

[EliGohar]

Thanks @diegozecchini for your detailed answer however this is not what I'm looking for.

In your solution, you calculate the duration for each object and then summed up but there is no reference for each minute in the hour, whether it was covered or not...

In addition, the calculation at the bottom of your answer (for Percent coverage) is wrong.

Eli. 

[diegozecchini]

Hi! ok, sorry it wasn't so clear for me.

If I understand correctly you want to evaluate whether each minute in the hour is covered by any object.
Create a field in the UI for each minute in the hour, ranging from :00 to :59. This can be simulated with ValueLoop or manually specifying the range if constrained to UI-only logic.

ValueLoop(0, 59)

then: for each minute, check whether it falls within the range of any object's start time and end time using set analysis or logical expressions.

Example
Sum(
If(
(StartTime <= MakeTime(14, ValueLoop(0, 59)) AND
EndTime >= MakeTime(14, ValueLoop(0, 59))),
1,
0
)
)
This will create a binary result for each minute: 1 if the minute is covered and 0 otherwise.

Finally, sum up the results of the coverage check for all minutes in the hour.

[Daniel_Pilla · Accepted solution]

Hi @EliGohar ,

@diegozecchini 's solution was really close. It just double counted minutes if there was overlap. Here is an alternative:

Count(DISTINCT
	If(
        (Time(Round([start time], 1/24/60)) <= MakeTime(Hour([start time]), ValueLoop(0, 59)) AND
        Time(Round([end time], 1/24/60)) >= MakeTime(Hour([start time]), ValueLoop(0, 59))),
        ValueLoop(0, 59)
	)
) / 60

 Place this in a table with the dimension:

=Hour([start time])

 Note that this should really be done in the load script, as it will not perform well at scale and is unnecessary to process on the frontend.

Cheers,

[diegozecchini]

hi @Daniel_Pilla thanks for correction!

[EliGohar]

@Daniel_Pilla Thank you very much! I will check the solution and update 🙂