Can Powerpivot transform this data

[bertusavius]

I have a source table that contains time information about shifts people work:

	E	F	G	H
Source Table
John
Frank

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]37[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]38[/TD]
[TD="bgcolor: #366092, align: right"]name[/TD]
[TD="bgcolor: #366092"]date[/TD]
[TD="bgcolor: #366092"]start-time[/TD]
[TD="bgcolor: #366092"]stop-time[/TD]

[TD="align: center"]39[/TD]

[TD="align: right"]1-1-2012[/TD]
[TD="align: right"]13:00[/TD]
[TD="align: right"]15:45[/TD]

[TD="align: center"]40[/TD]

[TD="align: right"]15-8-2012[/TD]
[TD="align: right"]8:45[/TD]
[TD="align: right"]10:30[/TD]

</tbody>

Blad4​

I would like to transform this information into a pivot table using PowerPivot in order to get the following result:

	N	O	P	Q
1500-1600

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]28[/TD]
[TD="align: right"]Pivot Table[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="bgcolor: #366092, align: right"][/TD]
[TD="bgcolor: #366092"]John[/TD]
[TD="bgcolor: #366092"]Frank[/TD]
[TD="bgcolor: #366092"]James[/TD]

[TD="align: center"]30[/TD]
[TD="bgcolor: #95B3D7, align: right"]1-1-2012[/TD]
[TD="bgcolor: #95B3D7, align: right"][/TD]
[TD="bgcolor: #95B3D7, align: right"][/TD]
[TD="bgcolor: #95B3D7, align: right"][/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]1300-1400[/TD]
[TD="align: right"]1:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]
[TD="align: right"]1400-1500[/TD]
[TD="align: right"]1:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]

[TD="align: right"]0:45[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]
[TD="bgcolor: #95B3D7, align: right"]15-8-2012[/TD]
[TD="bgcolor: #95B3D7, align: right"][/TD]
[TD="bgcolor: #95B3D7, align: right"][/TD]
[TD="bgcolor: #95B3D7, align: right"][/TD]

[TD="align: center"]35[/TD]
[TD="align: right"]0800-0900[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0:15[/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/TD]
[TD="align: right"]0900-1000[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1:00[/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]
[TD="align: right"]1000-1100[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0:30[/TD]
[TD="align: right"][/TD]

</tbody>

Blad4​

What would be a good way to approach this problem?

 

[powerpivotpro]

I love this problem, been turning it over in my head for a few hours now. But I've concluded that PowerPivot isn't a big help for this particular problem. PowerPivot itself is not a good "data shaper" and what you need here is more granularity in your rows of data - you need three rows for John where you currently only have 1. PowerPivot is an aggregator - it helps you turn more rows into less, not vice versa. Just like normal pivots, but with lots more benefits.

 

[bertusavius]

Ok, I get what you mean.
I would use PowerPivot the wrong way like this.

I guess I need to work my dataset over using regular excel formulas to create the required granularity before I launch it into PowerPivot.

A bit of a disappointment nontheless.

 

[Fazza]

I don't have power pivot, but assume a normal Excel approach can be used. And for sure can be adopted for pivot pivot.

I've given the base table the normal defined name (for example via CTRL-F3) of "Details". Not a dynamic named range.

For convenience, set up another table. I called it "tblHours" with fields "start-time" and "stop-time".

0:00 and 1:00 for the first record
1:00 and 2:00 for the second record
and so on through the hours of the day until
23:00 and 24:00 for the final (24th) record

Save & close the file. From a new file, start the pivot table wizard (I'm in Excel 2003, btw, and use shortcut ALT-D-P). At the first step choose 'external data source' then follow the wizard: "Get Data", Excel files, OK, choose the source data file, OK, see the defined names. Select anything (ADD), just to get to the next step. Select the 'SQL' button and replace whatever is there by the SQL below. OK to enter that, see the results dataset, hit the 'open door' icon to exit MS Query, now choose the option to make the pivot table. Format the pivot table to achieve the exact arrangement desired. Exiting MS Query can take a while. Not just because of the Cartesian product in the query. If it hangs up, please use CTRL-ALT-DEL to bring up the task manager, which can then be dismissed. This might need to be done a couple of times.

I didn't test this much, so it might need a slight tweak of the SQL still - I'm thinking of hours near the limits; either the first or last hours of the day as they can need special attention to get the math correct. Please modify as required.

regards

SELECT D.[Date] AS [MyDate], FORMAT(H.[start-time],'hh:mm') & ' - ' & FORMAT(H.[stop-time],'hh:mm') AS [MyTime], IIF(D.[stop-time]<H.[STOP-TIME],D.[STOP-TIME],H.[STOP-TIME]) IIF(D.[start-time] ->H.[start-time],D.[start-time],H.[start-time])  AS [Value], D.name
FROM Details D, tblHours H
WHERE HOUR(D.[start-time]) <= HOUR(H.[start-time]) AND HOUR(D.[stop-time])+1 >= IIF(HOUR(H.[stop-time]) = 0, 24, HOUR(H.[stop-time]))

 

[Laurent C]

Using PowerPivot V2, you could define a measure like that (Not thoroughly tested):
=SUMX(
CROSSJOIN('dim time buckets';source);
IF('dim time buckets'[start] >source[end-time]
|| 'dim time buckets'[end]<source[start-time] ;BLANK();
IF('dim time buckets'[end]<source[end-time]
;'dim time buckets'[end];source[end-time]) -
IF('dim time buckets'[start]>=source[start-time]
;'dim time buckets'[start];source[start-time])
)
)
This requires a dimension table containing your buckets (something like the following)
bucket start end
08:00 - 09:00 08:00 09:00
09:00 - 10:00 09:00 10:00
10:00 - 11:00 10:00 11:00
......

No relationship required.

EDIT: for the sake of simplicity, I worked with time not dates. If you choose this approach, you may have to adapt the measure definition or your fact table accordingly.</source[end-time]
</source[start-time]>

 

[JavierGuillen]

You can achieve the outcome you need entirely in PowerPivot by using a linked table and DAX measures:

First, import the following table as a linked Table (call it Time Range Lookup):

[TABLE="width: 258"]
<tbody>[TR]
[TD]time ranges[/TD]
[TD]start time[/TD]
[TD]end time[/TD]
[/TR]
[TR]
[TD]0700-0800[/TD]
[TD="align: right"]7:00:00 AM[/TD]
[TD="align: right"]8:00:00 AM[/TD]
[/TR]
[TR]
[TD]0800-0900[/TD]
[TD="align: right"]8:00:00 AM[/TD]
[TD="align: right"]9:00:00 AM[/TD]
[/TR]
[TR]
[TD]0900-1000[/TD]
[TD="align: right"]9:00:00 AM[/TD]
[TD="align: right"]10:00:00 AM[/TD]
[/TR]
[TR]
[TD]1000-1100[/TD]
[TD="align: right"]10:00:00 AM[/TD]
[TD="align: right"]11:00:00 AM[/TD]
[/TR]
[TR]
[TD]1100-1200[/TD]
[TD="align: right"]11:00:00 AM[/TD]
[TD="align: right"]12:00:00 PM[/TD]
[/TR]
[TR]
[TD]1200-1300[/TD]
[TD="align: right"]12:00:00 PM[/TD]
[TD="align: right"]1:00:00 PM[/TD]
[/TR]
[TR]
[TD]1300-1400[/TD]
[TD="align: right"]1:00:00 PM[/TD]
[TD="align: right"]2:00:00 PM[/TD]
[/TR]
[TR]
[TD]1400-1500[/TD]
[TD="align: right"]2:00:00 PM[/TD]
[TD="align: right"]3:00:00 PM[/TD]
[/TR]
[TR]
[TD]1500-1600[/TD]
[TD="align: right"]3:00:00 PM[/TD]
[TD="align: right"]4:00:00 PM[/TD]
[/TR]
</tbody>[/TABLE]


(you should add all time ranges but i only did here the relevant ones)


2) Add a DAX measure (identify checkin/checkout times):

CheckIn - CheckOut Time:=IF(
HASONEVALUE( 'Time Range Lookup'[time ranges] ) ,
IF(VALUES(Table1[start time]) >= VALUES('Time Range Lookup'[start time] ) &&
VALUES(Table1[start time]) < VALUES('Time Range Lookup'[end time] ) ,
"CheckIn",
IF(VALUES(Table1[end time]) >= VALUES('Time Range Lookup'[start time] ) &&
VALUES(Table1[end time]) < VALUES('Time Range Lookup'[end time] ) ,
"CheckOut" )
)
)


3) Identify work hours in between:

WorkHourRange:=IF(
[CheckIn - CheckOut Time] <> BLANK(),
[CheckIn - CheckOut Time],
IF(
IF(
HASONEVALUE( 'Time Range Lookup'[time ranges] ) ,
CALCULATE(
IF (
CONTAINS( ADDCOLUMNS( VALUES('Time Range Lookup'[time ranges] ), "check", [CheckIn - CheckOut Time] ), [check], "CheckIn" ) &&
NOT( CONTAINS( ADDCOLUMNS( VALUES('Time Range Lookup'[time ranges] ), "check", [CheckIn - CheckOut Time] ), [check], "CheckOut" ) )
, 1 ) ,
FILTER( ALL('Time Range Lookup'),
COUNTROWS(
FILTER( 'Time Range Lookup',
EARLIER( 'Time Range Lookup'[start time] ) < 'Time Range Lookup'[start time] ) )
)
)
)
<> BLANK(), "Work"
)
)

4) Create the final calculation for worked hours:

WorkedHours:=SWITCH(
[WorkHourRange],
"CheckIn",
FORMAT ( VALUES('Time Range Lookup'[end time]) - VALUES( Table1[start time] ), "H:MM") ,
"Work", "1:00" ,
"CheckOut",
FORMAT ( VALUES(Table1[end time]) - VALUES('Time Range Lookup'[start time]) , "H:MM")
)


In the pivot table, place the employee names on columns, dates (from Table1, your data) & time ranges (from the imported lookup table) on rows, and then DAX measure [WorkHourRange] on values. Remember to modify the format of the measure to be of Date type and display only the hours (you can do that in the measure editor window)


Javier Guillen
Senior BI Consultant
PowerPivot Blog: http://javierguillen.wordpress.com
Twitter: @javiguillen

 

[bertusavius]

Just back to let you guys know that I explored all your suggestions and that I solved the problem.
In the end I adopted the crossjoin solution by Laurent C.


SUMX(
CROSSJOIN('Tabel13';'Tijd');
IF(AND('Tabel13'[Starttijd] < 'Tijd'[Eind tijd] ; 'Tabel13'[Stoptijd] > 'Tijd'[Start tijd])
; if('Tijd'[Eind tijd] > Tabel13[Stoptijd] ; Tabel13[Stoptijd]; 'Tijd'[Eind tijd])
-
if('Tijd'[Start tijd] < 'Tabel13'[Starttijd];'Tabel13'[Starttijd];'Tijd'[Start tijd])
;0) *(24)
)


I really appreciate the time and effort by the thread contributors.



Only problem I'm facing now is performance issues although the source data is only about 20K records.
When I change a slicer of the PT, I sometimes have to wait for a full 5 minutes for the PT to refresh.
"OLAP query processing" it says.

I'm off trying to remove some unused measures to see if this helps.

 

[Laurent C]

5 minutes is indeed too long. The CROSSJOIN might be too much.

You might want to try this variation:
=SUMX('Tijd';
SUMX('Tabel13';
IF(AND('Tabel13'[Starttijd] < 'Tijd'[Eind tijd] ; 'Tabel13'[Stoptijd] > 'Tijd'[Start tijd])
; if('Tijd'[Eind tijd] > Tabel13[Stoptijd] ; Tabel13[Stoptijd]; 'Tijd'[Eind tijd]) -
if('Tijd'[Start tijd] < 'Tabel13'[Starttijd];'Tabel13'[Starttijd];'Tijd'[Start tijd])
;0) *(24)
))

 

[Laurent C]

Where has the the Edit button gone?

Anyway, you might also want to try the following:
=SUMX(
GENERATE(Tijd;
FILTER(Tabel13; 'Tabel13'[Starttijd] < 'Tijd'[Eind tijd] && 'Tabel13'[Stoptijd] > 'Tijd'[Start tijd] )
)
; if('Tijd'[Eind tijd] > Tabel13[Stoptijd] ; Tabel13[Stoptijd]; 'Tijd'[Eind tijd])
- if('Tijd'[Start tijd] < 'Tabel13'[Starttijd];'Tabel13'[Starttijd];'Tijd'[Start tijd])
) * 24

 

[JavierGuillen]

Im a bit confused. I thought the output needed to be a break down by time range as shown in the initial post. Is this the case still? Or is the idea to get the total hours worked per day?

When I apply Laurent's expression, with names on columns and dates on rows, I get 2.75 for John (on 1/1/2012) and 1.75 for frank on (8/15/2012). Should the output be a total per day, fractional representation of hours worked, or should it be the time worked broken by hour range?