Need Help with pivot table and week numbers

S

Sineadd

Board Regular
Joined
Apr 24, 2019
Messages
58
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi

I am trying to get pivot table to show me sales data for a particular store for a each week number of the year.
I have my main data in the below format

DateMonthWeek NoDayMMM-DDWarehouseStoreSales Values
30/12/2019​
Dec
1​
30​
Dec-30WestShop 1
20​
31/12/2019​
Dec
1​
31​
Dec-31WestShop 1
20​
01/01/2020​
Jan
1​
1​
Jan-01WestShop 1
20​
02/01/2020​
Jan
1​
2​
Jan-02WestShop 1
20​
03/01/2020​
Jan
1​
3​
Jan-03WestShop 1
20​
04/01/2020​
Jan
1​
4​
Jan-04WestShop 1
20​
05/01/2020​
Jan
1​
5​
Jan-05WestShop 1
20​
06/01/2020​
Jan
2​
6​
Jan-06WestShop 1
1​
07/01/2020​
Jan
2​
7​
Jan-07WestShop 1
1​
08/01/2020​
Jan
2​
8​
Jan-08WestShop 1
1​
09/01/2020​
Jan
2​
9​
Jan-09WestShop 1
1​
10/01/2020​
Jan
2​
10​
Jan-10WestShop 1
1​
11/01/2020​
Jan
2​
11​
Jan - 11WestShop 1
1​
12/01/2020​
Jan
2​
12​
Jan -12WestShop 1
1​

As week 1 of 2020 starts on the 30/12/2019 it doesn't display correct figures for week 1 when I create pivot table like below.
Can anyone please help me and let me know what I need to do so that it will display the correct week 1 figures for week 1 2019 - Weeks run from Monday - Sunday

Row Labels
1​
2​
total
2019
Shop 1
100​
7​
107​
total
100​
7​
107​
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
Like this?
Book1
ABCDEFGHIJKLMN
16DateMonthWeek NoDayMMM-DDWarehouseStoreSales ValuesyearSum of Sales ValuesStore
1712/30/2019Dec13012/30/2020WestShop 1202019yearWeek NoShop 1Grand Total
1812/31/2019Dec13112/31/2020WestShop 1202019201914040
1901/01/2020Jan1101/01/2020WestShop 12020202019 Total4040
2001/02/2020Jan1201/02/2020WestShop 120202020201100100
2101/03/2020Jan1301/03/2020WestShop 12020202020277
2201/04/2020Jan1401/04/2020WestShop 12020202020 Total107107
2301/05/2020Jan1501/05/2020WestShop 1202020Grand Total147147
2401/06/2020Jan2601/06/2020WestShop 112020
2501/07/2020Jan2701/07/2020WestShop 112020
2601/08/2020Jan2801/08/2020WestShop 112020
2701/09/2020Jan2901/09/2020WestShop 112020
2801/10/2020Jan21001/10/2020WestShop 112020
2901/11/2020Jan21101/11/2020WestShop 112020
3001/12/2020Jan21201/12/2020WestShop 112020
Sineadd
Cell Formulas
RangeFormula
I17:I30I17=YEAR([@Date])
 
Upvote 0
Or based on your presentation:
Book1
KLMNO
16Sum of Sales ValuesWeek No
17yearStore12Grand Total
182019Shop 14040
192019 Total4040
202020Shop 11007107
212020 Total1007107
22Grand Total1407147
Sineadd


or streamlined:
Book1
KLMNO
16Sum of Sales ValuesWeek No
17yearStore12Grand Total
182019Shop 14040
192020Shop 11007107
20Grand Total1407147
Sineadd
 
Upvote 0
with Power Query
shop.png

is that what you want?
 
Last edited:
Upvote 0
cyrilbrd said:
Or based on your presentation:
Book1
KLMNO
16Sum of Sales ValuesWeek No
17yearStore12Grand Total
182019Shop 14040
192019 Total4040
202020Shop 11007107
212020 Total1007107
22Grand Total1407147
Sineadd


or streamlined:
Book1
KLMNO
16Sum of Sales ValuesWeek No
17yearStore12Grand Total
182019Shop 14040
192020Shop 11007107
20Grand Total1407147
Sineadd
Click to expand...

Week 1 should show the result 140 for sales values as week one is from the 30/12/2019-5/01/2020

Having the pivot table to display the correct amount for each week will work no problem for all other weeks and it will only happen if week 1 includes dates from previous year or if week 52 runs into dates for the following year.

I had originally inserted an extra column in the data table with the heading year and manually changed the 30th and 31st Dec 2019 to the year 2020 and that will then display the correct data in week numbers for me but I was hoping there was an easier way than having to manually change the year for dates that are in the previous year but count as week 1 in the current year.
 
Upvote 0
sandy666 said:
with Power Query
View attachment 5986
is that what you want?
sandy666 said:
with Power Query
View attachment 5986
is that what you want?
Click to expand...
Hi

Thanks for the reply

Im looking to get the format as below but with week 1 would equal 140 going by the data entered for the dates above 30th Dec 2019 - 5th Jan 2020

I got it to display as below by inserting an extra column with year in the main data table and manually changing the year for the dec dates to 2020 so it would pull those figures as week 1 2020 but i was hoping there was an easier way beside manually updating the year for certain dates as it will happen most years for week 1 / week 52 / week 53

1581036833429.png
Click to expand...
 
Upvote 0
like this?
shop4.png

warehouse column and store column can be removed if necessary
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
15
Views
1K
Wishmaster40
W
J
  • Solved
Excel Formula to highlight most recent record
Replies
3
Views
94
etaf
etaf
sakrams
  • Question
Excel VBA script for week number and the weekly dates (Concatenated)
Replies
8
Views
172
Cubist
C
M
  • Solved
How to count certain month from a column
Replies
3
Views
95
Joe4
Joe4
B
  • Question
countifs and remove data completed
2
Replies
10
Views
369
MuchToLearn
M

Forum statistics

Threads
1,214,653
Messages
6,120,750
Members
448,989
Latest member
mariah3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top