Quarterly Payment Split

Hi,

Trying to find a solution to this issue.

I've attached a snippet of some data.

1) First, I want to filter for 'Service Charge' under the 'Rent Type' column

2) Where the 'Frequency' column is '/Month', I want the data to be left untouched.

Where the 'Frequency' column is 'Total', I want the data to be left untouched again.
Where the 'Frequency' column is '/Quarter', I want the data to be divided by 3 and split across the months following.

E.G. where Mar - 2022 has 15000, I would want 5000 to be shown for Apr - 2022, May - 2022 and Jun - 2022. Mar - 2022 would then become zero.

Book26433.xlsx

Tips and Tricks

Common Use Cases

Best Practices

Join

In Database

Data Investigation

In-Database

Accepted answers

ArtApa

@tomtingley - I changed a few things for you. Please let me know, if it meets your requirements.

PaymentSplitAA 0.2.yxmd

binu_acs

@tomtingley updated the workflow (your dummy file had NULL values but in the full file it is 0 which was causing the issue)

QuarterUpdate_150523.yxzp

All comments

binu_acs

@tomtingley One way of doing this

QuarterUpdate_150523.yxzp

ArtApa

@tomtingley - Here is a possible solution:

PaymentSplitAA.yxmd

tomtingley

Thanks @binu_acs @ArtApa !!

However, when I try with my full data set which includes date to 2027, it doesn't work. I've attached the file.

ALL MONTHS.xlsx

binu_acs

@tomtingley Can you provide the expected result for some records or all records?

tomtingley

@binu_acs When I tried using the 'ALL MONTHS' input, my 2022 data ended up at the end and the quarterly payments did not divide by 3 as you showed earlier in your workflow. Instead, nothing changes.

ArtApa

@tomtingley - I changed a few things for you. Please let me know, if it meets your requirements.

PaymentSplitAA 0.2.yxmd

binu_acs

@tomtingley updated the workflow (your dummy file had NULL values but in the full file it is 0 which was causing the issue)

QuarterUpdate_150523.yxzp

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335