How to subtract the payment from the Bill amount if stacked onto each other in Alteryx This is the sample Raw Data i have TransactionAmountType11000Invoice 121000Payment 31221Invoice 141221Payment 5254Invoice 16100Invoice 27354Payment 8544Invoice 19544Payment And this is the desired output i want which subtract all the Invoice amount from the Payment amount. TransactionAmountType11000Invoice21000Payment 0 31221.5Invoice41221Payment 0.5 5254Invoice6100Invoice7354Payment 0 8544Invoice9544Payment 0

Subtract Stacked values

How to subtract the payment from the Bill amount if stacked onto each other in Alteryx

This is the sample Raw Data i have

Transaction	Amount	Type
1	1000	Invoice 1
2	1000	Payment

3	1221	Invoice 1
4	1221	Payment

5	254	Invoice 1
6	100	Invoice 2
7	354	Payment

8	544	Invoice 1
9	544	Payment

And this is the desired output i want which subtract all the Invoice amount from the Payment amount.

Transaction	Amount	Type
1	1000	Invoice
2	1000	Payment
	0
3	1221.5	Invoice
4	1221	Payment
	0.5
5	254	Invoice
6	100	Invoice
7	354	Payment
	0
8	544	Invoice
9	544	Payment
	0

Transformation

Accepted answers

CoG

This can be accomplished by combining the power of a few tools, namely Formula Tool, Summarize Tool, and Union! There is some separate setup that must be implemented before those tools will work for you, but that's not too complicated (as can be seen below):

Hope this helps and Happy Solving!

Sample Workflow.yxmd

All comments

Rajat_Rehria

payment can also be multiple times so in that case i need to subtract the summation of all the payment from summation of all the respective invoice amount which stacked above the Payments.