Flat file - Subtract other row with precondition

[ernesttpr]

Hi, how do I subtract another row in a flat file with precondition. 
What I'm trying to do is - if Major Heading column is 'Sales', then I would like to subtract the 'Amount' for the 'Period' 'Jun-20' from 'May-20' with the same 'Account Code / Name'

[davidskaife]

Hi @ernesttpr 

Please see attached solution. A filter to restrict to just Sales rows, a crosstab grouped by the field(s) you require, the headers being the Periods, and the value being the amount. A formula tool to then subtract one field from the other.

The formula tool can be expanded to include multiple calculations if required

Cheers,

David

[ernesttpr]

Hi - Thanks that was helpful. I should have been more specific, there are actually 12 months in the 'Period' Column, thus trying to achieve the below.

If Major Heading column is 'Sales', then I would like to subtract the 'Amount' for the 'Period' 'Jun-20' from 'May-20' with the same 'Account Code / Name'. If 'Period' is 'May'20', then I would like to subtract the 'Apr-20' 'Amount' following the same logic. Would need to do the same for periods Feb-20 to Dec-20, however if the value is Jan-20, then no subtraction is needed.

There are also 10 distinct values in Major Heading Column, and I'm trying to do the above logic for 5 of them.

Thanks in advance!

[davidskaife]

Hi @ernesttpr How would you like the output to look? I can expand the workflow to take into account the logic but an understanding of the end result would be good.

[ernesttpr]

Hi David,

Thanks! I've actually managed to work it out.