Suggestions or recommendations for weekly report changes

[zinah]

Hi,

I have a weekly terms report that shows the weekly terms over the week. What I'm looking for is to find a way that can show the changes Week over Week without me copying every week's report into a tab, then build separate pivots for each week, and do manual calculations. Any recommendation for this situation?
I have field that include the Report Date which differentiate every week from the other.


Thank you!

 

[Jeffrey Mahoney]

Without seeing your data I'm guessing at how you have it laid out. Not that you're doing this, but the first thing people start doing is separating their data into different sheets. This makes it hard to summarize. A proper database style table has a single row for each entry that has all the pertinent data, meaning a column for every defining piece of information. Yes, there will be lots of repeat bits, but each row will be different in some way. This makes it very easy to summarize and create pivot tables.

 

[zinah]

Thank you for your input. Here's a sample table of the expected headers of my weekly terms report. Is there a way I can keep track of the changes week over week? As you know we may have terms during the week.

Report Date	Employee ID	Full Legal Name	Termination Date - All	Primary Termination Reason	Termination Category

And below are sample tables (pivot tables) that I usually do on weekly basis and calculate the difference manually

Count of Employee ID	Column Labels									Count of Employee ID	Column Labels
	Jul	Aug	Sep	Oct	Nov	Dec	Grand Total				Jul	Aug	Sep	Oct	Nov	Dec	Grand Total
Row Labels										Row Labels
Dept A		2		2			4			Dept A		2		2			4
Dept B	3	18	19	22	2	1	65			Dept B	3	18	19	20	1	1	62
Dept C		6					6			Dept C		6					6
Dept D		1		1			2			Dept D		1		1			2

Without seeing your data I'm guessing at how you have it laid out. Not that you're doing this, but the first thing people start doing is separating their data into different sheets. This makes it hard to summarize. A proper database style table has a single row for each entry that has all the pertinent data, meaning a column for every defining piece of information. Yes, there will be lots of repeat bits, but each row will be different in some way. This makes it very easy to summarize and create pivot tables.

 

[Jeffrey Mahoney]

What are your parameters in your pivot tables? Ask yourself if there is any columns that can help with summarizing the results. Do you need a calculation column to show week number? Don't you have a hire date? Showing your needed end result will help us determine all the fields necessary to give those results easily.

When you get finished, your main table will have the entire year so that you can summarize it any way you want.

 

[zinah]

What are your parameters in your pivot tables? Ask yourself if there is any columns that can help with summarizing the results. Do you need a calculation column to show week number? Don't you have a hire date? Showing your needed end result will help us determine all the fields necessary to give those results easily.

I guess I edited my respond before you had a look, below are the pivot tables that I usually do on weekly basis. Which is basically get the terms by department and month of termination. And yes, I do have Hire Date and Term Date. Does that help by any chance?

Count of Employee ID	Column Labels									Count of Employee ID	Column Labels
	Jul	Aug	Sep	Oct	Nov	Dec	Grand Total				Jul	Aug	Sep	Oct	Nov	Dec	Grand Total
Row Labels										Row Labels
Dept A		2		2			4			Dept A		2		2			4
Dept B	3	18	19	22	2	1	65			Dept B	3	18	19	20	1	1	62
Dept C		6					6			Dept C		6					6
Dept D		1		1			2			Dept D		1		1			2

 

[Jeffrey Mahoney]

Looks like your main table would need a column for Department. I don't see that your pivot report shows any weekly results, only monthly. Do you have a standardized Termination Reason or is it free text? Same for Termination Category? You might later decide to filter on either of those two fields; with consistent values gives you better reporting abilities.

I'm just acting like a consultant here, like I do for all my clients. Take any of these suggestions or ideas or leave them based on your needs.

 

[zinah]

Looks like your main table would need a column for Department. I don't see that your pivot report shows any weekly results, only monthly. Do you have a standardized Termination Reason or is it free text? Same for Termination Category? You might later decide to filter on either of those two fields; with consistent values gives you better reporting abilities.

I'm just acting like a consultant here, like I do for all my clients. Take any of these suggestions or ideas or leave them based on your needs.

Thank you so much for being my consultant
Yes, I forgot to add the Dept Field to my standard table.
And I have a standardized termination reason and category. Do I need to consider Power Query path? if yes, how it's possible?

 

[Jeffrey Mahoney]

How do you get your data now? From other people? From other applications? Do you enter the data yourself or have others enter the data directly into the workbook?

 

[zinah]

How do you get your data now? From other people? From other applications? Do you enter the data yourself or have others enter the data directly into the workbook?

I get the data from a scheduled Workday weekly report. Then I manually add a tab for each week and build new pivot table of each week on a summary tab, i.e. as of this week I have 3 tabs that each one represents week of October.

 

[Jeffrey Mahoney]

Ok. At this point, instead of creating a separate sheet for each week, you're going to combine them. Make the columns in your combined report line up with the columns from your report. When you import the data, you can create a new pivot sheet for each week. Make sense?