PIVOT Q.

[CarlosV]

Hi,

So I have several income and expenses accounts that I get the acumulated balance at the end of every month, and I have made a matrix formula (complicated and very heavy and time consuming to calculate due to the amount of data that needs to be calculated) to be able to show the real income or expence in an specific month.

I would love to be able to do this in a pivot but, cant figure out a way of doing the calculation.

An example of the data will be something like this.

lets say I had 100 of monthly expense in acount 123456 wich refers to xyz expense since the beginning of the year, the data I would get is:

jan 123456 xyz 100
feb 123456 xyz 200
mar 123456 xyz 300
abr 123456 xyz 400

So each month i would get the acumulated expense of the on going year for every acount, and to be able to determine the value of the month I have to substract the current month from the acumulated value until the previous month.

If any one can help me with this, I would really apreciate it.

 

[Luke M]

You might be able to use the Difference From previous, in the Value field settings. See attached to see if this would work. Downside is that for the first month, it shows 0 (since there's no previous).

 

[CarlosV]

You might be able to use the Difference From previous, in the Value field settings. See attached to see if this would work. Downside is that for the first month, it shows 0 (since there's no previous).

Thanks Luke, that would definetly work for myself, but for presenting its kind of a no no..

I will still try it out to see If It works better on a bigger scale that what I have..

thanks again!

 

[Luke M]

Hi Carlos,

The other approach would be to build a formula that does the subtractions, creating a "new" field for the PT. A lot depends on the layout of your original data, and where in the process you can make changes (ie., can we change source data, PT, after PT and before a dashboard, etc.).