Sub total amount calculation

[Vapour02]

Hello all professionals,I need help to calculate the credit amount but also to consider the debit amount and date. For example. If debit amount is on 2023-10-07 and the next one is on 2023-10-17 I need to calculate the total credit amount between these two debit amounts date range and then again calculate the total amount between 2023-10-17 and 2023-10-19 and then again the total amount between 2023-10-19 and 2023-10-27.

Also when the total amount is calculated I need to find the difference between the debit amount and the total of all credit amount between the date ranges. Attached is the sample for reference.

[Jonathan-Sherman]

Hi @Vapour02,

Could you also share the output you're looking to get to?

Kind regards,

Jonathan

[Vapour02]

@Jonathan-Sherman Ofcourse. I need something like this. Please refer to attachment.

[OllieClarke]

Hi @Vapour02 

This workflow mimics your output (other than the calc column which I took a different approach for). I am a bit worried that credit amounts are being counted across multiple debit amounts, but that might be expected by you...

Let me know if you have any questions,

Ollie

[Vapour02]

@OllieClarke Thank you so much for the solution this worked. However I guess,I put up the wrong output results. I actually need to exclude the first credit amount when the debit amount was added and calculate from the next. So the 21000 amount on 23-10-07 should be excluded and it should be calculated from 23-10-08 till 23-10-17 . Also if the amount is less than 1000 in debit amount it should be excluded so in this case the next calculation should start from 23-10-18 till 23-10-26.

I hope I'm able to explain better. 

[OllieClarke · Accepted solution]

Hi @Vapour02 

I think I've understood you, although it would have been easier with another output.

A couple of simple changes (changing the >0 to >=1000 in the multi-row and normal formula tool, and adding a skip first row per group sample) and then Robert is your mother's brother.

[Vapour02]

Hey @OllieClarke Thank you so much for the solution, much appreciated. This worked as I expected. I just have one doubt why does it shows the same amount in the 'Total Credit' ? Is it possible to show the calculation of two amounts in 'Credit amount' in the 'Total Credit' column. For example. 42000+60000 = 102000, so is it possible that 102000 is calculated in the 'Total Credit' column and thus goes on like 102000+900000 = 1002000 in the next row and so on further. And when a new 'Debit amount ' is added it should reset and start again with the new calculation. I hope I'm able to explain.

Amount.      Total credit 

42000

60000.         102000

900000.       1002000

Something like this. Thanks in advance.

[OllieClarke]

Hi @Vapour02 

That was a hangover from your initial desired output (with the double counting of the first rows). 

Since you changed your output, we can actually simplify the workflow a lot. 

This I hope is everything you've asked for:

[Vapour02]

@OllieClarke The solution before this worked much better for me but thank you, much appreciated your help. However I'm facing a new challenge now since this was only for one account I might have multiple accounts in future. I tried to run the same solution on multiple accounts but it's messing up the data and the output. Attached is the sample for your reference. Can you please help me with that it would be much appreciated. 

[OllieClarke · Accepted solution]

Hi @Vapour02, quite an easy fix, just make sure to group by [Account] in the multi-row formula. I also changed the setup to give rows which don't exist a 0 value. As this lets the first row of each account start counting.

I couldn't match your output again, as for the 5678 account, you seem to be double counting the 5k on 2022-09-02. I think the workflow I've got here is correct though.

I think this thread speaks to the importance of providing an accurate picture of what you're asking for. @SeanAdams wrote a great blog about exactly this.https://community.alteryx.com/t5/Maveryx-Community-Resources/Posting-for-Fastest-Possible-Solution/t...