Changing sales data from Cumulative to week totals

[MadHatsJess]

Hi

I have three years worth of sales data by week for 50 products. I want to do a pivot (grouped by month and year) to show YOY sales in a Pivot chart and use slicers to select the product.

The problem I have is that the data I have is rolling so the pivot table adds the sales together. See example below

01/01/2016 - 10
07/01/2016 - 12
14/01/2016 - 20

01/01/2017 - 15
07/01/2017 - 22
14/01/2017 - 30

01/01/2018 - 20
01/01/2018 - 32
01/01/2018 - 40

Is there a quick way to change the data to how many sales where made that week?

Or

A better way of doing this?

I have a lot of data and have tried ordering the data and doing Week 2 minus 1 but its very time consuming to update

Thanks :D

 

[sheetspread]

Assuming your data is sorted by product then date, drag a difference formula down with an if statement that resets at each new product and year.

 

[MadHatsJess]

Hi

That's what I do at the minute but without the IF statement to reset at the product/year. If I could get that to work it would be perfect!

 

[MadHatsJess]

Could you help with a basic outline to the IF?

 

[sheetspread]

Something like:

Excel 2010
	A	B	C	D
1	Product	Date	Cumulative Sales	Weekly Sales
2	A	1/1/2016	10
3	A	1/8/2016	12	2
4	A	1/15/2016	20	8
5	A	1/1/2017	15	15
6	A	1/8/2017	22	7
7	A	1/15/2017	30	8
8	A	1/1/2018	20	20
9	A	1/8/2018	32	12
10	A	1/15/2018	40	8
11	B	1/1/2016	5	5
12	B	1/8/2016	7	2
13	B	1/15/2016	15	8
14	B	1/1/2017	9	9
15	B	1/8/2017	13	4
16	B	1/15/2017	19	6
17	B	1/1/2018	3	3
18	B	1/8/2018	11	8
19	B	1/15/2018	15	4
Sheet10

Cell Formulas
Range	Formula
D3	=IF(AND(A3=A2,YEAR(B3)=YEAR(B2)),C3-C2,C3)