Spreading payments across multiple months

[stahooven]

Hi!  I've been working through a formula which is starting to give me fits.  I am trying to allocate a charge across multiple months for financial reporting purposes.  I am given a start date and an end date with a max date range of 60 days so it could possibly spread a portion of the charge across three months.  As such, I built the following three formulas to get my periods identified (the calculation on the spreading of the charges is pretty straightforward once I get this locked down).  The frustrating part is in the preview window, these work perfectly but then when I run them in alteryx it blows up.  Would love any feedback here:

Month 1

DateTimeTrim([Start Date],'lastofmonth') - I will always have at least one month where the charge needs to go

Month 2

if datetimetrim([End Date], 'lastofmonth')=[Month 1]
then null() else datetimetrim(datetimeadd([Month 1], 1, "month"), 'lastofmonth') endif

Month 3

if isnull([Month 2]) then null() elseif datetimetrim([End Date], 'lastofmonth')=[Month 2] then null() else datetimetrim(datetimeadd([Month 2], 1, "month"), 'lastofmonth') endif

So for example, my first row of data shows a start date of 2020-02-23 and an end date of 2020-02-23.  When I run these formulas, I get the following:

Month 1 = 2020-02-29 (this is correct)

Month 2 = 2020-03-31 (this is incorrect - should be null)

Month 3 = 2020-04-30 (this is incorrect - should be null)

[tristank]

Hi Stahooven!

This is interesting because I tested everything using your formulas and fields and everything seemed to work (see picture below). I messed around with the data types too but they still worked out.

Do you have a sample workflow to share where the error is popping up? And what do you mean by blow up?

For now all I can recommend is restarting your Alteryx and double checking the calculations :(

Good luck keep us updated!

[stahooven]

Attached is a sample workflow - it reproduces the error I am getting in record 1.  "Blow up" might just be me being dramatic?

[Blake_E]

Have you tried running it with and without AMP enabled to see if the error is the same with both engines?

[stahooven]

I have - errors are consistent.

[Blake_E]

@stahooven  your issue is that the datetimetrim produces a datetime output and your formula compares it to a date.  The preview pane in the formula tool is correctly calculating, but the formula tool does not handle the comparison of different date types correctly.   I was able to get it working correctly by wrapping any datetimetrim functions in ToDate().

Note: none of your test data carries values into HL Month 3.  I verified your formulas work correctly when the End Date crosses into the third month.

[CharlotteDavis99]

Hi there,

I see you're working on charge allocation across multiple months. Your formulas seem solid, but the discrepancy between preview and Alteryx execution is frustrating. Double-check conditions for Months 2 and 3 to ensure accuracy. Keep iterating, and let me know if you need more help.