Solved: band time to cover fixed band time - Qlik Community

Report Inappropriate Content · ‎2015-12-15

Hi all,

I'd like to understand if there is a way to understand how much a band time is covered.

I've got a table with WARD,DATA,NAME_EMP,TIME_IN,TIME_OUT.

I'd like to understand on a day base how much is the band time 08:00 to 20:00 covered?

Example:

Question1:20/01/2015 in WARD1 the band time had been covered totally (at least 1 person was in the ward during the day) or only the 80% was covered.

Question2:there were at least 1 person for 100%, 30% by 2 person,15% for 3 person (there were 3 person at the same time for 15% of the fixed band 08:00 to 20:00)

Can you help me?

I could do this only by iterative programming.

Any suggestion ?

THANK YOU ALL FOR YOUR TIME.

Amos

swuehl · ‎2015-12-15

You could separate your original intervals into distinct non-overlapping subintervals like Henric demonstrated in

IntervalMatch and Slowly Changing Dimensions

(p. 17-18)

Using the subintervals, you can quite easily answer both questions.

Here is the script I used:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';
SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';


OriginalIntervals:
LOAD *, TmpSPID & '|' & TIME_IN & '|' & TIME_OUT as OriginalIntervalID;
LOAD *, AutoNumber(WARD & DATE) as TmpSPID INLINE [
WARD,DATE,NAME_EMP,TIME_IN,TIME_OUT
A, 15.12.2015, Tom, 08:00, 16:00
A, 15.12.2015, Jerry, 15:00, 20:00
A, 15.12.2015, Mickey, 09:00, 11:00
A, 15.12.2015, Minnie, 10:00, 16:00
B, 15.12.2015, Tick, 08:00, 16:00
B, 15.12.2015, Trick, 10:00, 18:00
];


Let vEpsilon = Pow(2,-37);
// Find all breakpoints of the intervals.
TempSubIntervals:
Load distinct TmpSPID as SPID, TIME_IN as SubFromTime Resident OriginalIntervals;
Load distinct TmpSPID as SPID, TIME_OUT as SubFromTime Resident OriginalIntervals;
// Create intervals from single dates.
SubIntervals:
Load SPID, Time(SubFromTime) as SubFromTime, SPID & '|' & SubToTime as SubIntervalID,
Dual(SubToTime, SubToTime - $(#vEpsilon)) as SubToTime
Where not IsNull(SubToTime) ;
Load SPID, Time(SubFromTime) as SubFromTime,
If(SPID = Previous(SPID), Previous(SubFromTime)) as SubToTime
Resident TempSubIntervals
Order By SPID, SubFromTime Desc;


Drop Table TempSubIntervals;


// ============ Create the bridge table between the two interval types ============
TmpBridgeTable:
IntervalMatch (SubToTime, SPID)
Load distinct TIME_IN, TIME_OUT, TmpSPID as SPID resident OriginalIntervals;


BridgeTable:
LOAD *, AutoNumber(SubIntervalID, 'SUBID') as SubIntervalIDNum;
Load 
  SPID & '|' & SubToTime as SubIntervalID, 
  SPID & '|' & TIME_IN & '|' & TIME_OUT as OriginalIntervalID 
  Resident TmpBridgeTable;


Drop Field TmpSPID;
Drop table TmpBridgeTable;

In the front end, to answer question 1, create a straight table chart with WARD and DATE and as expression

=Sum(SubToTime-SubFromTime) / 0.5

WARD	DATE	Coverage %
		183,33%
A	15.12.2015	100,00%
B	15.12.2015	83,33%

To answer question 2: Create a straight table with WARD, DATE and a calculated dimension

=Aggr(Count(DISTINCT NAME_EMP), SubIntervalID)

Sort the calculated dimension by numeric ascending.

Then as expression, use

=Rangesum(Below(Sum(SubToTime-SubFromTime) / 0.5, 0, NoOfRows()))

WARD	DATE	# Emp	Coverage %
A	15.12.2015	1	100,00%
A	15.12.2015	2	58,33%
A	15.12.2015	3	16,67%
B	15.12.2015	1	83,33%
B	15.12.2015	2	50,00%

See also the QVW attached

Hope this helps,

Stefan

View solution in original post

Gysbert_Wassenaar · ‎2015-12-15

Something like this will get you the coverage per employee per ward per date:

MyData:

LOAD *, DurationInBand / 2 as PercentageCovered

LOAD

WARD,

DATE,

NAME_EMP,

TIME_IN,

TIME_OUT,

Interval(RangeMin(TIME_OUT,MakeTime(20)) - RangeMax(TIME_IN, MakeTime(8)),'hh:mm') as DurationInBand

FROM

...source_table...

;

If you want to know about coverage by multiple employees then you'll probably have to decide on an interval like hour, quarter or 5-minute interval so each employees presence can be matched to the intervals. That way you can count the number of employees in each interval.

talk is cheap, supply exceeds demand

swuehl · ‎2015-12-15