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)
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!
Have you tried running it with and without AMP enabled to see if the error is the same with both engines?
I have - errors are consistent.
@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.
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.