Pivot-Table - Difference from total runing-in

D

DockS

New Member
Joined
Dec 18, 2015
Messages
12
Hello everyone,

I am building a cash management file and I managed to get it well untill I want to finalize my pivot-table report.

The thing I am trying to obtain is exactly describe here : https://www.youtube.com/watch?v=q5M20UwxY1Q but for a confusing reason I don't obtain the same result..


I have daily cash operations on several account for several entities and I want a "balance sheet" made from the initial balance + the operations, grouped by month & a variance month-on-month.

I manage to obtain that by creating a "sum, running total in" in my pivot-table. The wierd thing is when I add again the field amount and choose "difference from, date, previous" I have a calculation that doesn't make any sense to me.

I searched a bit, and another wierd thing is that the "variance" or difference is ok if I just had my operations amount field but I then have a first variance column with the full amount of january which is quite misleading.

I'm a bit surprised as I am sure I used to produce that kind of simple pivot table calculation right in the past...


example (with difference from previous) :

[TABLE="width: 597"]
<colgroup><col><col span="6"></colgroup><tbody>[TR]
[TD] [/TD]
[TD]janv[/TD]
[TD] [/TD]
[TD]févr[/TD]
[TD] [/TD]
[TD]mars[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]k €[/TD]
[TD]var[/TD]
[TD]k €[/TD]
[TD]var[/TD]
[TD]k €[/TD]
[TD]var[/TD]
[/TR]
[TR]
[TD]St2[/TD]
[TD="align: right"](161,2)[/TD]
[TD] [/TD]
[TD="align: right"](88,2)[/TD]
[TD="align: right"] 234,3 [/TD]
[TD="align: right"](150,2)[/TD]
[TD="align: right"](135,1)[/TD]
[/TR]
[TR]
[TD]CHARGES EXTERNES[/TD]
[TD="align: right"](21,0)[/TD]
[TD] [/TD]
[TD="align: right"](29,5)[/TD]
[TD="align: right"] 12,6 [/TD]
[TD="align: right"](51,0)[/TD]
[TD="align: right"](13,1)[/TD]
[/TR]
[TR]
[TD]HONORAIRES[/TD]
[TD="align: right"](8,4)[/TD]
[TD] [/TD]
[TD="align: right"](28,1)[/TD]
[TD="align: right"](11,2)[/TD]
[TD="align: right"](41,6)[/TD]
[TD="align: right"] 6,1 [/TD]
[/TR]
[TR]
[TD]IMPOTS ET TAXES[/TD]
[TD="align: right"](32,8)[/TD]
[TD] [/TD]
[TD="align: right"](32,8)[/TD]
[TD="align: right"] 32,8 [/TD]
[TD="align: right"](32,8)[/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD]INT[/TD]
[TD] - [/TD]
[TD] [/TD]
[TD="align: right"] 90,4 [/TD]
[TD="align: right"] 90,4 [/TD]
[TD="align: right"] 83,6 [/TD]
[TD="align: right"](97,2)[/TD]
[/TR]
[TR]
[TD]XX[/TD]
[TD="align: right"](29,9)[/TD]
[TD] [/TD]
[TD] - [/TD]
[TD="align: right"] 59,7 [/TD]
[TD] - [/TD]
[TD="align: right"](29,9)[/TD]
[/TR]
[TR]
[TD]SALAIRES ET CHARGES[/TD]
[TD="align: right"](69,1)[/TD]
[TD] [/TD]
[TD="align: right"](88,2)[/TD]
[TD="align: right"] 50,0 [/TD]
[TD="align: right"](108,3)[/TD]
[TD="align: right"](1,1)[/TD]
[/TR]
</tbody>[/TABLE]



example (with added column without running total in :
[TABLE="width: 600"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 597"]
<colgroup><col><col span="6"></colgroup><tbody>[TR]
[TD] [/TD]
[TD]janv[/TD]
[TD] [/TD]
[TD]févr[/TD]
[TD] [/TD]
[TD]mars[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]k €[/TD]
[TD]var[/TD]
[TD]k €[/TD]
[TD]var[/TD]
[TD]k €[/TD]
[TD]var[/TD]
[/TR]
[TR]
[TD]St2[/TD]
[TD="align: right"](161,2)[/TD]
[TD="align: right"](161,2)[/TD]
[TD="align: right"](88,2)[/TD]
[TD="align: right"] 73,1 [/TD]
[TD="align: right"](150,2)[/TD]
[TD="align: right"](62,0)[/TD]
[/TR]
[TR]
[TD]CHARGES EXTERNES[/TD]
[TD="align: right"](21,0)[/TD]
[TD="align: right"](21,0)[/TD]
[TD="align: right"](29,5)[/TD]
[TD="align: right"](8,5)[/TD]
[TD="align: right"](51,0)[/TD]
[TD="align: right"](21,5)[/TD]
[/TR]
[TR]
[TD]HONORAIRES[/TD]
[TD="align: right"](8,4)[/TD]
[TD="align: right"](8,4)[/TD]
[TD="align: right"](28,1)[/TD]
[TD="align: right"](19,7)[/TD]
[TD="align: right"](41,6)[/TD]
[TD="align: right"](13,5)[/TD]
[/TR]
[TR]
[TD]IMPOTS ET TAXES[/TD]
[TD="align: right"](32,8)[/TD]
[TD="align: right"](32,8)[/TD]
[TD="align: right"](32,8)[/TD]
[TD] [/TD]
[TD="align: right"](32,8)[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]INT[/TD]
[TD] - [/TD]
[TD] [/TD]
[TD="align: right"] 90,4 [/TD]
[TD="align: right"] 90,4 [/TD]
[TD="align: right"] 83,6 [/TD]
[TD="align: right"](6,8)[/TD]
[/TR]
[TR]
[TD]XX[/TD]
[TD="align: right"](29,9)[/TD]
[TD="align: right"](29,9)[/TD]
[TD] - [/TD]
[TD="align: right"] 29,9 [/TD]
[TD] - [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]SALAIRES ET CHARGES[/TD]
[TD="align: right"](69,1)[/TD]
[TD="align: right"](69,1)[/TD]
[TD="align: right"](88,2)[/TD]
[TD="align: right"](19,1)[/TD]
[TD="align: right"](108,3)[/TD]
[TD="align: right"](20,2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Target :
[TABLE="width: 597"]
<colgroup><col><col span="6"></colgroup><tbody>[TR]
[TD] [/TD]
[TD]janv[/TD]
[TD] [/TD]
[TD]févr[/TD]
[TD] [/TD]
[TD]mars[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]k €[/TD]
[TD]var[/TD]
[TD]k €[/TD]
[TD]var[/TD]
[TD]k €[/TD]
[TD]var[/TD]
[/TR]
[TR]
[TD]St2[/TD]
[TD="align: right"](161,2)[/TD]
[TD] [/TD]
[TD="align: right"](88,2)[/TD]
[TD="align: right"] 73,1 [/TD]
[TD="align: right"](150,2)[/TD]
[TD="align: right"](62,0)[/TD]
[/TR]
[TR]
[TD]CHARGES EXTERNES[/TD]
[TD="align: right"](21,0)[/TD]
[TD] [/TD]
[TD="align: right"](29,5)[/TD]
[TD="align: right"](8,5)[/TD]
[TD="align: right"](51,0)[/TD]
[TD="align: right"](21,5)[/TD]
[/TR]
[TR]
[TD]HONORAIRES[/TD]
[TD="align: right"](8,4)[/TD]
[TD] [/TD]
[TD="align: right"](28,1)[/TD]
[TD="align: right"](19,7)[/TD]
[TD="align: right"](41,6)[/TD]
[TD="align: right"](13,5)[/TD]
[/TR]
[TR]
[TD]IMPOTS ET TAXES[/TD]
[TD="align: right"](32,8)[/TD]
[TD] [/TD]
[TD="align: right"](32,8)[/TD]
[TD] - [/TD]
[TD="align: right"](32,8)[/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD]INT[/TD]
[TD] - [/TD]
[TD] [/TD]
[TD="align: right"] 90,4 [/TD]
[TD="align: right"] 90,4 [/TD]
[TD="align: right"] 83,6 [/TD]
[TD="align: right"](6,8)[/TD]
[/TR]
[TR]
[TD]XX[/TD]
[TD="align: right"](29,9)[/TD]
[TD] [/TD]
[TD] - [/TD]
[TD="align: right"] 29,9 [/TD]
[TD] - [/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD]SALAIRES ET CHARGES[/TD]
[TD="align: right"](69,1)[/TD]
[TD] [/TD]
[TD="align: right"](88,2)[/TD]
[TD="align: right"](19,1)[/TD]
[TD="align: right"](108,3)[/TD]
[TD="align: right"](20,2)[/TD]
[/TR]
</tbody>[/TABLE]



If someone could help.

Have a nice day
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
You must log in or register to reply here.

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

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