If Quarterly Payments, Convert into Monthly

Question

In the attached data set, I'm looking to create a flow so that if we have numbers coming through only every quarter, I want to change that into monthly numbers.
I want to do this for 'Service Charge' only, so leave 'Base Rent' as it is.

So, for example, in Row 5, we have numbers in Dec 22, Mar 23, Jun 23, etc.

In Mar23, we have -1,816,200.

Instead of one number in Mar23, I would want to divide that by 3 and spread across Jan23, Feb23 and Mar23.

So it would go from

Jan23: 0
Feb23: 0
Mar23: 1,186,200

To become

Jan23: 605,400
Feb23: 605,400
Mar23: 605,400

SeanAdams · Answer

Yip - my thinking on this would be:
- use a generate rows tool if needed to bulk out the rows - you may not need to do this.

- Create a "Quarter" field to allow you to group months into quarters

- Figure out the max for each year & quarter

- Divide this max by 3 for each quarter

- Join this all back to the raw data, on year and quarter

- and then update the Value column for every row for this quarter to be this new value.     If you add this as a new field, you can check your math, and hten use a select tool to get rid of the old one

Christina_H · Answer

Try this

Quarterly payments.yxmd

cjaneczko · Answer

I am thinking you'll want to Transpose the data and use a Multi Row formula. Then create a new field that identifies the quarters. For each quarter, divide the month payment by 3 and fill it down or up. Then Cross tab the data back into the original format.