Amount and Days Difference

[Vapour02]

Hi all professionals, I need help to get a desired output about amounts and days differences. So I have Account, Debit amount and Credit amount. I need the days difference between two Debit amounts and sub-total value of Credit amount lying between two Debit amounts. Also remaining amount of Debit amounts minus the sub-total value of Credit amount. Also the first line wherever there is Debit amount should not be considered and the output should be calculated from next line and there can be missing dates which should be considered as it is as they are actually missing. The calculation should start again as the account changes. Hope I'm able to explain as much as I can. Attached is the sample for reference. Kindly help with a desired solution. Thank you.

[aatalai]

I would use the multi row 

formula to caluate sum of debit with running total

[running total debit (newly created field):row-1]+debit

grouping by account ID

and then do something with credit.

Then use a regular formula tool to do the difference of the 2 running totals. You could go fancy with this codnitonal formula

If debit !=0 then running toatl credit - running total debit else NULL() endif.

@Vapour02 let me know if this helps

[aatalai]

Sorry that just gets you amount difference, for date difference I would use the filter tool to look at those with debit>0

then with the true output use multi row to calulate the differcne between dates datediff([date:row},[date:row-1],"unit") again goruping by acocunt id

And then joining the two ouptus based on dates and accoutn id

[Vapour02]

Hey @aatalai Thank you for the info, but I have already tried using the multi row formula created a group by debits and then grouping by account and the sum of credit. But I'm still not getting the required output. However if I filter a single account and do the same it works but doesn't work on two accounts. 

[binuacs]

@Vapour02 can you explain your logic here again? how did you calculate subtotal as 5000 for the account 9090?

[Vapour02]

Hello @binuacs , I did not do any calculation since it's the only line item I took it as it is. And then started from next line item since there's a new Debit amount. So calculated from debit amounts 30000 to 55000 and then the subtotal of them. 

[Vapour02]

@binuacs As there is no other line item so the 5000 was taken as it is and the remaining below were calculated.