I want to calculate difference in a dataset

[Sshasnk]

I have a dataset where I finding very to difficult to calculate rows

Example:

Fund	amount	prior year amount	type
ABC	123	145	Investment
ABC	145	156	Total
LMN	1456	15	Investment
LMN	159	23	Total
XYZ	456	56	Investment
XYZ	23	14	Total

In About all, I have to find a difference in investment and total (Type column)

Fund	amount	prior year amount	type
ABC	123	145	Investment
ABC	145	156	Total
ABC	-22	-11	Difference
LMN	1456	15	Investment
LMN	159	23	Total
LMN	1297	-8	Difference
XYZ	456	56	Investment
XYZ	23	14	Total
XYZ	433	42	Difference

I am not sure if it will be easy to transpose and do it or it should be done easily in row formula

[ShankerV]

Hi @Sshasnk 

It can be easily achieved with the help of Summarize tool.

Then join the source and Summarize tool to get your output.

Many thanks

Shanker V

[ShankerV]

Hi @Sshasnk 

Here is the desired output for you.

My workflow:

Many thanks

Shanker V

[davidskaife]

Hi @Sshasnk 

Here is a way of doing it:

Firstly calculate the differences using two Multi-Row Formula tools, then filter out unwanted fields. Rename the calculated fields and create the "Difference" type field, then union back together and finally sort in order.

[ShankerV]

@Sshasnk 

My workflow explanation in detail, 

Step 1: Input the data

Step 2: Ignore my select tool, as I used to modify the datatype from Byte to Int

Use the Multi Field Formula.

Step 3: One more Multi Field formula

Step 4: Summarize tool

[Sshasnk]

@ShankerV  Can you attach the workflow

[ShankerV]

@Sshasnk 

Trying to attach, give me sec. Will fix the issue and attach the same.

Many thanks

Shanker V

[ShankerV]

@Sshasnk 

Step 5: Formula tool

Step 6: Union tool

Step 7: Sort tool

Output

Hope your issue is resolved. Feel free to mark helpful answers as a solution, so that future users with the same issue can find them easier!!!!

Many thanks

Shanker V

[KrishnaChithrathil]

@Sshasnk 

Here's another way we can think of doing.

[ShankerV]

Hi @Sshasnk 

Took some time to fix the issue faced in saving the workflow. Here is my workflow attached for you.

Many thanks

Shanker V