Help analysing a timesheet database

%3CLINGO-SUB%20id%3D%22lingo-sub-2567444%22%20slang%3D%22en-US%22%3EHelp%20analysing%20a%20timesheet%20database%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2567444%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20community!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20having%20trouble%20analysing%20a%20set%20of%20timesheet%20data%20made%20of%20the%20following%20columns%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUser%3C%2FP%3E%3CP%3EActivity%20Type%3C%2FP%3E%3CP%3EDate%20the%20activity%20was%20performed%3C%2FP%3E%3CP%3EDate%20that%20the%20activity%20was%20reported%3C%2FP%3E%3CP%3EActivity%20duration%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20data%20work%20as%20follows%3A%3C%2FP%3E%3CP%3EIf%20user%20%22X%22%20spent%202%20hours%20in%20a%20meeting%20with%20a%20client%20in%2001%2F01%2F2021%2C%20and%20included%20this%20information%20on%20his%20timesheet%20only%20in%2001%2F03%2F2021%2C%20the%20row%20regarding%20this%20activity%20would%20show%2C%20respectively%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EX%3C%2FP%3E%3CP%3EMeeting%3C%2FP%3E%3CP%3E01%2F01%2F2021%3C%2FP%3E%3CP%3E01%2F03%2F2021%3C%2FP%3E%3CP%3E2%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20summarize%20this%20data%20in%20a%203%20column%20table%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20first%20one%2C%20is%20%22Date%22%3B%3C%2FP%3E%3CP%3EThe%20second%20is%20%22Ammount%20of%20hours%20worked%20on%20said%20date%22%3B%20and%3C%2FP%3E%3CP%3EThe%20third%20is%20%22Ammount%20of%20hours%20reported%20on%20said%20date%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20illustrate%20this%2C%20consider%20a%20user%20that%20works%208%20hours%20per%20day%2C%20monday%20to%20friday%2C%20and%20reports%208%20hours%20per%20day.%20His%20timesheet%20and%20his%20summarized%20data%20should%20look%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22113px%22%20height%3D%2230px%22%3E%3CSTRONG%3E%3CU%3EUser%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22113px%22%20height%3D%2230px%22%3E%3CSTRONG%3E%3CU%3EActivity%20Type%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22113px%22%20height%3D%2230px%22%3E%3CSTRONG%3E%3CU%3EActivity%20Date%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22125px%22%20height%3D%2230px%22%3E%3CSTRONG%3E%3CU%3EReport%20Date%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22101px%22%20height%3D%2230px%22%3E%3CSTRONG%3E%3CU%3EDuration%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22113px%22%20height%3D%2230px%22%3EX%3C%2FTD%3E%3CTD%20width%3D%22113px%22%20height%3D%2230px%22%3E%3CP%3Eexample%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%22113px%22%20height%3D%2230px%22%3Eday%201%3C%2FTD%3E%3CTD%20width%3D%22125px%22%20height%3D%2230px%22%3Eday%201%3C%2FTD%3E%3CTD%20width%3D%22101px%22%20height%3D%2230px%22%3E8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22113px%22%20height%3D%2230px%22%3EX%3C%2FTD%3E%3CTD%20width%3D%22113px%22%20height%3D%2230px%22%3Eexample%3C%2FTD%3E%3CTD%20width%3D%22113px%22%20height%3D%2230px%22%3Eday%202%3C%2FTD%3E%3CTD%20width%3D%22125px%22%20height%3D%2230px%22%3Eday%202%3C%2FTD%3E%3CTD%20width%3D%22101px%22%20height%3D%2230px%22%3E8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22113px%22%20height%3D%2230px%22%3EX%3C%2FTD%3E%3CTD%20width%3D%22113px%22%20height%3D%2230px%22%3Eexample%3C%2FTD%3E%3CTD%20width%3D%22113px%22%20height%3D%2230px%22%3Eday%203%3C%2FTD%3E%3CTD%20width%3D%22125px%22%20height%3D%2230px%22%3Eday%203%3C%2FTD%3E%3CTD%20width%3D%22101px%22%20height%3D%2230px%22%3E8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22113px%22%20height%3D%2230px%22%3EX%3C%2FTD%3E%3CTD%20width%3D%22113px%22%20height%3D%2230px%22%3Eexample%3C%2FTD%3E%3CTD%20width%3D%22113px%22%20height%3D%2230px%22%3Eday%204%3C%2FTD%3E%3CTD%20width%3D%22125px%22%20height%3D%2230px%22%3Eday%204%3C%2FTD%3E%3CTD%20width%3D%22101px%22%20height%3D%2230px%22%3E8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%22113px%22%20height%3D%2230px%22%3EX%3C%2FTD%3E%3CTD%20width%3D%22113px%22%20height%3D%2230px%22%3Eexample%3C%2FTD%3E%3CTD%20width%3D%22113px%22%20height%3D%2230px%22%3Eday%205%3C%2FTD%3E%3CTD%20width%3D%22125px%22%20height%3D%2230px%22%3Eday%205%3C%2FTD%3E%3CTD%20width%3D%22101px%22%20height%3D%2230px%22%3E8%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E%3CU%3E%3CSTRONG%3EDate%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E%3CU%3E%3CSTRONG%3EHours%20Worked%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E%3CU%3E%3CSTRONG%3EHours%20Reported%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E%3CP%3EDay%201%20(mon)%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E8%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3EDay%202%20(tue)%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E8%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3EDay%203%20(wed)%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E8%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3EDay%204%20(thu)%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E8%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3EDay%205%20(fri)%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E8%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E8%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20consider%20a%20user%20that%20works%20the%20same%208%20hours%20per%20day%2C%20but%20reports%20them%20all%20only%20on%20friday.%20His%20timesheet%20and%20his%20summarized%20data%20should%20look%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%3E%3CU%3E%3CSTRONG%3EUser%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E%3CU%3E%3CSTRONG%3EActivity%20Type%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E%3CU%3E%3CSTRONG%3EDate%20Activity%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E%3CU%3E%3CSTRONG%3EDate%20Reported%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E%3CU%3E%3CSTRONG%3EDuration%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%3EX%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E%3CP%3Eexample%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3Eday%201%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3Eday%205%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%3EX%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3Eexample%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3Eday%202%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3Eday%205%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%3EX%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3Eexample%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3Eday%203%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3Eday%205%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%3EX%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3Eexample%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3Eday%204%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3Eday%205%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E8%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2220%25%22%3EX%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3Eexample%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3Eday%205%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3Eday%205%3C%2FTD%3E%3CTD%20width%3D%2220%25%22%3E8%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%221%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E%3CU%3E%3CSTRONG%3EDate%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E%3CU%3E%3CSTRONG%3EHours%20Worked%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E%3CU%3E%3CSTRONG%3EHours%20Reported%3C%2FSTRONG%3E%3C%2FU%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E%3CP%3EDay%201%20(mon)%3C%2FP%3E%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E8%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3EDay%202%20(tue)%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E8%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3EDay%203%20(wed)%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E8%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3EDay%204%20(thu)%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E8%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3EDay%205%20(fri)%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E8%3C%2FTD%3E%3CTD%20width%3D%2233.333333333333336%25%22%20height%3D%2230px%22%3E40%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20is%20that%20i%20cant%20seem%20to%20create%20a%20pivot%20table%20summarizing%20the%20data%20in%20this%20way.%20As%20i%20need%20to%20be%20able%20to%20use%20the%20columns%20%22User%22%20and%20%22Activity%20Type%22%20as%20filters%2C%20I%20assume%20that%20the%20pivot%20table%20is%20the%20best%20option%20to%20achieve%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAm%20I%20correct%20in%20my%20assumption%3F%20If%20so%2C%20how%20can%20I%20achieve%20my%20objective%3F%3C%2FP%3E%3CP%3EOr%20am%20I%20wrong%20anf%20the%20pivot%20table%20cant%20summarize%20the%20data%20in%20such%20a%20way%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2567444%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

Hello community!

 

I am having trouble analysing a set of timesheet data made of the following columns:

 

User

Activity Type

Date the activity was performed

Date that the activity was reported

Activity duration

 

The data work as follows:

If user "X" spent 2 hours in a meeting with a client in 01/01/2021, and included this information on his timesheet only in 01/03/2021, the row regarding this activity would show, respectively:

 

X

Meeting

01/01/2021

01/03/2021

2

 

I want to summarize this data in a 3 column table:

 

The first one, is "Date";

The second is "Ammount of hours worked on said date"; and

The third is "Ammount of hours reported on said date".

 

To illustrate this, consider a user that works 8 hours per day, monday to friday, and reports 8 hours per day. His timesheet and his summarized data should look like this:

 

UserActivity TypeActivity DateReport DateDuration
X

example

day 1day 18
Xexampleday 2day 28
Xexampleday 3day 38
Xexampleday 4day 48
Xexampleday 5day 58

 

DateHours WorkedHours Reported

Day 1 (mon)

88
Day 2 (tue)88
Day 3 (wed)88
Day 4 (thu)88
Day 5 (fri)88

 

Now consider a user that works the same 8 hours per day, but reports them all only on friday. His timesheet and his summarized data should look like this:

 

UserActivity TypeDate ActivityDate ReportedDuration
X

example

day 1day 58
Xexampleday 2day 58
Xexampleday 3day 58
Xexampleday 4day 58
Xexampleday 5day 58

 

DateHours WorkedHours Reported

Day 1 (mon)

80
Day 2 (tue)80
Day 3 (wed)80
Day 4 (thu)80
Day 5 (fri)840

 

The problem is that i cant seem to create a pivot table summarizing the data in this way. As i need to be able to use the columns "User" and "Activity Type" as filters, I assume that the pivot table is the best option to achieve this.

 

Am I correct in my assumption? If so, how can I achieve my objective?

Or am I wrong anf the pivot table cant summarize the data in such a way?

 

Thanks in advance!

0 Replies