Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
andrewmo
Creator
Creator

Summing with intervalmatch

Hello

I am trying to build a display that shows the utilization of each surgical block in an operating room.

To do so I have to create three main tables:

A case details table that has the case times and locations

A master calendar table

A block calendar table that contains possible dates for each block based on the day of the week and week number of the month.

I then need to use a combination of all the above tables to be able to see the count of cases going at any minute of a given day versus the amount of rooms available for that specific block at that specific time.

I would like to see something similar to this:

MINOFDAY.png

The issue I have as seen in the pic above is I am not able to capture distinct minute of day (notice 8:21 and 8:28) nor am I able to figure out the logic to capture the number of cases going at that time of day.

I have attached a few dummy cases to my logic in the QVW below.

Please let me know if you have any suggestions.

Thanks again

1 Solution

Accepted Solutions
sunny_talwar

Check the attached

View solution in original post

11 Replies
adamdavi3s
Master
Master

Hi Andrew,

I'm pretty sure I've got a theatre utilisation dashboard I created before which I can share

I'll check my server when I have a moment (later this evening)

sunny_talwar

Try this

BLOCKMINS:

NoConcatenate

Load DISTINCT interval(Floor(frac(BlkMinOfDayRAW), 1/1440),'h:mm') as BlkMinOfDay,ServLine,Dt,BlockLoc,StartTime as BlkStart ,EndTime as BlkEnd Resident BLOCKTIME;

INNER JOIN (BLOCKMINS)

IntervalMatch(BlkMinOfDay,ServLine,Dt,BlockLoc)

LOAD StartTime as BlkStart, EndTime as BlkEnd

Resident BLOCKCAL;

andrewmo
Creator
Creator
Author

Hi Sunny

Thanks for the advice. While this did solve the duplicate minute of day issue, now it appears the intervalmatch is off somehow:

MINOFDAY.png

8:27 should have a 2, but displays one. My expression for the Available column is =count(BlkStart) which comes from the BLOCKMINS load.

sunny_talwar

Or may be round

BLOCKMINS:

NoConcatenate

Load DISTINCT interval(Round(frac(BlkMinOfDayRAW), 1/1440),'h:mm') as BlkMinOfDay,ServLine,Dt,BlockLoc,StartTime as BlkStart ,EndTime as BlkEnd Resident BLOCKTIME;

INNER JOIN (BLOCKMINS)

IntervalMatch(BlkMinOfDay,ServLine,Dt,BlockLoc)

LOAD StartTime as BlkStart, EndTime as BlkEnd

Resident BLOCKCAL;

sunny_talwar

Try Round instead of Floor

andrewmo
Creator
Creator
Author

Awesome! That looks like it should do the trick for the Available column, any advice for the Going column?

I thought the INMINS load statement would work but I can't figure it out:

INMINS:

NoConcatenate

LOAD DISTINCT interval(frac(MinOfDay),'h:mm') as BlkMinOfDayTEST,Dt,ServLine,Loc, count(distinct LOG_ID) as Cnt

RESIDENT CASESFIN

GROUP BY interval(frac(MinOfDay),'h:mm'),Dt,ServLine,Loc;

I was thinking my expression for Going should be =if(BlkMinOfDayTEST = BlkMinOfDay,Cnt,0) but it does not return any results

sunny_talwar

I am not sure what you are counting within Going? Would you be able to elaborate a little?

andrewmo
Creator
Creator
Author

I want to be able to see the amount of LOG_IDs whose InTm and OutTm fall in the range of the BlkMinOfDay.

For example:

LOG_ID 123 has an InTm of 08:20 and an OutTm of 14:07

LOG_ID 345 has an InTm of 10:33 and an OutTm of 11:18

On the table with the BlkMinOfDay I would expect to see from 8:20-10:33 2 Available and 1 going, but then from 10:33-11:18 2 Available and 2 Going; then back to 2 Available and 1 going at 11:19..

Does that make sense?

sunny_talwar

Check the attached