topic band time to cover fixed band time in QlikView

band time to cover fixed band time

Tue, 15 Dec 2015 13:09:25 GMT

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

Re: band time to cover fixed band time

Gysbert_Wassenaar — Tue, 15 Dec 2015 17:01:29 GMT

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.

Re: band time to cover fixed band time

swuehl — Tue, 15 Dec 2015 22:02:11 GMT

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%

Re: band time to cover fixed band time

Wed, 23 Dec 2015 15:17:00 GMT

Thank you so much.

Now I'm studying the slowly changing dimensions.

really interesting.