Hi
I need some help setting up a pivot table. I need to compare sales figures from 2 seperate years for example sales figures from week 1 2016 against sales figures for week 1 2017 for each store. I then need to be able to show the monetary difference between each year and the % difference also. I need to this for 14 stores. Can anybody point me in the right direction or advise if i have the table in the right format to be able to create pivot required.
I have sample data below of the way I have it set up
[TABLE="width: 377"]
<tbody>[TR]
[TD][TABLE="width: 377"]
<tbody>[TR]
[TD]Week No[/TD]
[TD]Date[/TD]
[TD]Store[/TD]
[TD]Sales Values[/TD]
[TD]Sales Returns[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]01/01/2016[/TD]
[TD]Store 1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]02/01/2016[/TD]
[TD]Store 1[/TD]
[TD]14556.35[/TD]
[TD]12847.35[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]03/01/2016[/TD]
[TD]Store 1[/TD]
[TD]27535.6[/TD]
[TD]13599.1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]04/01/2016[/TD]
[TD]Store 1[/TD]
[TD]17773.3[/TD]
[TD]9956.4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]05/01/2016[/TD]
[TD]Store 1[/TD]
[TD]23932.68[/TD]
[TD]16530.4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]06/01/2016[/TD]
[TD]Store 1[/TD]
[TD]7260.6[/TD]
[TD]5942.6[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]01/01/2017[/TD]
[TD]Store 1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]02/01/2017[/TD]
[TD]Store 1[/TD]
[TD]24477.8[/TD]
[TD]20783.8[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]03/01/2017[/TD]
[TD]Store 1[/TD]
[TD]14059.9[/TD]
[TD]11858.599[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]04/01/2017[/TD]
[TD]Store 1[/TD]
[TD]14200.69[/TD]
[TD]14770.699[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]05/01/2017[/TD]
[TD]Store 1[/TD]
[TD]6198.5[/TD]
[TD]7744.2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]06/01/2017[/TD]
[TD]Store 1[/TD]
[TD]18234.68[/TD]
[TD]15949.85[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]07/01/2017[/TD]
[TD]Store 1[/TD]
[TD]28103[/TD]
[TD]17144[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Ideally would like data to display like the below. Any help greatly appreciated
[TABLE="width: 357"]
<colgroup><col><col span="3"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 357"]
<colgroup><col><col span="3"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]2016[/TD]
[TD]2017[/TD]
[TD]Variance[/TD]
[TD]% Variance[/TD]
[/TR]
[TR]
[TD]Sales Value [/TD]
[TD]500[/TD]
[TD]100[/TD]
[TD]-400[/TD]
[TD]-80%[/TD]
[/TR]
[TR]
[TD]Sales Returns[/TD]
[TD]200[/TD]
[TD]100[/TD]
[TD]-100[/TD]
[TD]-50%[/TD]
[/TR]
</tbody>[/TABLE]
Many Thanks
Sinead
I need some help setting up a pivot table. I need to compare sales figures from 2 seperate years for example sales figures from week 1 2016 against sales figures for week 1 2017 for each store. I then need to be able to show the monetary difference between each year and the % difference also. I need to this for 14 stores. Can anybody point me in the right direction or advise if i have the table in the right format to be able to create pivot required.
I have sample data below of the way I have it set up
[TABLE="width: 377"]
<tbody>[TR]
[TD][TABLE="width: 377"]
<tbody>[TR]
[TD]Week No[/TD]
[TD]Date[/TD]
[TD]Store[/TD]
[TD]Sales Values[/TD]
[TD]Sales Returns[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]01/01/2016[/TD]
[TD]Store 1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]02/01/2016[/TD]
[TD]Store 1[/TD]
[TD]14556.35[/TD]
[TD]12847.35[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]03/01/2016[/TD]
[TD]Store 1[/TD]
[TD]27535.6[/TD]
[TD]13599.1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]04/01/2016[/TD]
[TD]Store 1[/TD]
[TD]17773.3[/TD]
[TD]9956.4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]05/01/2016[/TD]
[TD]Store 1[/TD]
[TD]23932.68[/TD]
[TD]16530.4[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]06/01/2016[/TD]
[TD]Store 1[/TD]
[TD]7260.6[/TD]
[TD]5942.6[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]01/01/2017[/TD]
[TD]Store 1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]02/01/2017[/TD]
[TD]Store 1[/TD]
[TD]24477.8[/TD]
[TD]20783.8[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]03/01/2017[/TD]
[TD]Store 1[/TD]
[TD]14059.9[/TD]
[TD]11858.599[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]04/01/2017[/TD]
[TD]Store 1[/TD]
[TD]14200.69[/TD]
[TD]14770.699[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]05/01/2017[/TD]
[TD]Store 1[/TD]
[TD]6198.5[/TD]
[TD]7744.2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]06/01/2017[/TD]
[TD]Store 1[/TD]
[TD]18234.68[/TD]
[TD]15949.85[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]07/01/2017[/TD]
[TD]Store 1[/TD]
[TD]28103[/TD]
[TD]17144[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Ideally would like data to display like the below. Any help greatly appreciated
[TABLE="width: 357"]
<colgroup><col><col span="3"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 357"]
<colgroup><col><col span="3"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]2016[/TD]
[TD]2017[/TD]
[TD]Variance[/TD]
[TD]% Variance[/TD]
[/TR]
[TR]
[TD]Sales Value [/TD]
[TD]500[/TD]
[TD]100[/TD]
[TD]-400[/TD]
[TD]-80%[/TD]
[/TR]
[TR]
[TD]Sales Returns[/TD]
[TD]200[/TD]
[TD]100[/TD]
[TD]-100[/TD]
[TD]-50%[/TD]
[/TR]
</tbody>[/TABLE]
Many Thanks
Sinead
Last edited: