Grouping Like Amounts by Date Range

amcghee1990

Hello,

I'm trying to figure out how to take the data set below that is currently broken out by one month increments and show it in a date range by like amounts. For example, I currently have a summarize tool that is taking the min start date and max end date but that doesn't work in this scenario because we have a different amounts that falls between those dates (December 2021 and January 2022).

I need it to be like this

7/1/2021 - 11/30/2021 = 11,050

12/1/2021 - 12/31/2021 = 0.00

1/1/2022 - 1/31/2022 = 3996.17

2/1/2022 - 3/31/2022 = 11,050

NetSuite to Directus Workflow.yxmd

Date Time

Help

Optimization

Accepted answers

NicoleJohnson

Hi friend!! 🙂

I think the attached will get you what you need - the trick is to use the Multi-Row tool to determine your "buckets" of like-amounts first, and then group by the bucket & min start date/max end date. See attached - you'll want to be careful with data types (use fixed decimal, not double) and then add your lookup for GL accounts back in (I took it out since that data set wasn't included.) You might also have to tinker with grouping in your Multi-Row tool if there is more than 1 lease_ref that you're trying to do this for all at the same time.

Sample solution:

Hope this helps!!

Cheers,

AMcGhee Example.yxmd

All comments

atcodedog05

Hi @amcghee1990

You would need to share the input file too.

Happy to help : )

Oops here you go!

there is a csv file 😅

amcghee1990

Sorry! I can't attached the complete file because it's too big. This is a breakdown of the original CSV file. The other files aren't relevant to what I'm trying to do. The other file is just adding the account number but that doesn't change the dates and dollars!

Alteryx Data.csv

atcodedog05

Hi @amcghee1990

I am getting error as column missing in excel file.

can you provide the extracted data from the screenshot step I can build the logic from there.

Happy to help : )

I'm sorry!

if this is monthly grouping why is the red highlighted dates there which are not the same month end.

I need it to be like this

7/1/2021 - 11/30/2021 = 11,050

12/1/2021 - 12/31/2021 = 0.00

1/1/2022 - 1/31/2022 = 3996.17

2/1/2022 - 3/31/2022 = 11,050

amcghee1990

The above is a lease payment schedule. The amounts are the same but December and January we got a rent payment discount. Does that make sense? The original file is only showing in one month increments but I need my output to group dates like above.

amcghee1990

Basically I need to group the dates by amount but if there's a change in amount it needs to be broken out by month like the December and January payments above.

NicoleJohnson

Hi friend!! 🙂

Sample solution:

Hope this helps!!

Cheers,

AMcGhee Example.yxmd

amcghee1990

Thanks so much Nicole! I knew you could help 🙂 Hope your doing well!

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

Aguisande 317