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

[NicoleJohnson · Accepted solution]

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,

NJ

[amcghee1990]

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