#50 Timesheet for worked hours 2016 and PowerView report with stats
This post is about the new version (V3) of the Excel I made to track your worked hours, the "
Timesheet 2016". For my post about the previous version (V2), see:
http://worktimesheet2014.blogspot.com.es/2015/04/timesheet-for-worked-hours-2015-v3-with.html
Timesheet-V3 does not have any new features comparing with V2, the only difference is that V3 has the 2016-calendar (V2 was for 2015). So in this post I won´t repeat the explanation about the timesheet, for this see the post of Timesheet-V2 (2015).
But there is one thing new for the 2016-Timesheet: I made a new Excel with a PowerView report with statistics about worked days and holidays at year-, month- and week-level. The Excel with the Timesheet also has a worksheet with statistics, but these are using the 'traditional' pivot-tables, while the new Excel with PowerView uses PowerPivot Tables which has some new nice features which I´ll show below.
To show how this Timesheet works, I made a demo in which I registered check-in and check-out times for all (working-)days in 2016, and also the days off (vacations, sick, personal days).
If you want to use this Excel to track your worked hours, you should delete all my input for this demo (green columns in sheet-2: 'Tabla').
In fig.1 you can see the Excel with the PowerView report.
NB:
- this report shows Month-stat. for Dec. and Week-stat. for week 50 (which starts on Monday (Spanish: Lunes) 22 (Dec.) (see filter-pane, to the right of report-canvas)
- the report-titles are generated, and have some Spanish:
'por' = by
'y' = and
And in fig.2 you can see the Excel with the Timesheet-data (fig.2) and -statistics (fig.3).
In the rest of this post, the figures are from the Excel with the PowerView report.
fig.1 Excel with Powerview-report for Timesheet
fig.2 : Excel with Timesheet (table)
fig.3 : Excel with Timesheet statistics
In fig.4 you can see that if you filter in the bar-chart Hours Worked per Month for 1 month , e.g. Dec., then also the other report-element at year-level, the pie-chart Days Off, is filtered for this month.
fig.4: filtering in report (month = Dec.)
In fig.5 you can see the result after I clicked in fig.1 in pie-chart Days Off on the blue-part of the pie-chart, labeled Code2: 'VA' (= vacations) and in fig.6-7 you can see how to drill-down in this pie-chart to Code3: VA0 and VA1 (VA0: vacations-days of last year, VA1: vacations-days of actual year).
fig.5: Pie-chart Days Off (not worked)
fig.6: Pie-chart Days Off , config. drill-down Code2-> Code3 (see field: Color)
fig.7: Pie-chart Days Off , result drill-down Code2-> Code3
To get the data from the Timesheet-Excel (worksheet-2, data-table 'Tabla2') into the PowerView Report-Excel I used PowerQuery (see fig. 8) and I imported the data in the PowerPivot datamodel. After that I added some DAX-formulas in this datamodel, e.g. to calculate the worked time in hours, with decimals (in the Timesheet-Excel, the time is registered in minutes):
HoursWorked := ROUND([Ttotal3]/60;2)
and to get some more 'friendly names' for fields and field-values in the pivot-table, and in English rather than Spanish, I used e.g. this formula:
IsWorkingDay:=IF([EsLaborable]=1;"Y";"N")
And in the PowerPivot datamodel I also renamed some fields to more 'friendly names' and marked some fields as 'not visible in client', so that the final-user (you) only has the fields which are usefull in pivot tables (e.g. WorkedHours, but not 'Notes').
fig.8: PowerQuery to import Timesheet data
For the resulting PowerPivot datamodel, see fig.9-10.
fig.9: PowerPivot datamodel with DAX-formula
--
fig.10: PowerPivot datamodel with friendly field-names and hidden fields
As you can see, the new Timesheet statistics, with PowerView, are better then the old ones. Some people call Excel with the PowerBI-plugins (PowerPivot, PowerView, PowerQuery and PowerMap) 'Power Excel', and say 'Power Excel' is the best tool for all kinds of data-analysis, which results in an interesting discussion, see:
http://www.powerpivotpro.com/2015/06/no-more-apologies-excel-is-the-worlds-best-data-tool-period/
Some references:
* For a (free) course about data-analysis with MS Excel, by my old university, TU Delft (Delft University of Technoloy), see:
https://www.edx.org/xseries/data-analysis-business
* For a video with more details about how to create a timesheet with Excel, see:
https://youtu.be/vYdHi2m4IgE
*For a good video and blog about MS Excel and Powerview (and more Microsoft software), see:
http://www.wiseowl.co.uk/blog/s389/power-view.htm
https://youtu.be/8PUmuud7TYg
Downloads:
NB: The Excel with the Timesheet-data, which is input for the Excel with the PowerView-report, should be in this dir:
C:\tmp\zzz\TimeSheetWorkedHours_2016_DEMO_R1.xlsx
#Mirror 1: Google Drive (zip file with 2 Excel files):
zip-file with 2 Excel files:
https://goo.gl/OQFI0j
PDF:
https://goo.gl/23LLQs
#Mirror 2: Microsoft Onedrive (1 Excel file: Timesheet):
NB: this site has 'Excel-Online', so you can view my Excel-doc if you don´t have MS Excel on your PC
http://1drv.ms/1mO95vj
#Mirror 3: Scribd.com (1 PDF file: PowerView report
):
https://es.scribd.com/doc/295022256/Excel-TimeSheet-WorkedHours-2016-with-PowerPivot-report-for-Stats
