Sub total amount calculation

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.

Alteryx_Sample_data_Amount.xlsx

Workflow

Datasets

Developer Tools

Output

Help

Expression

Accepted answers

OllieClarke

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.

subtotals_2.yxmd

OllieClarke

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/ta-p/178481

subtotals_4.yxmd

All comments

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.

Alteryx_Sample_data_Amount_addon.xlsx

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703