on 11-19-201311:08 PM - edited on 07-27-202111:47 PM by APIUserOpsDM
To begin, there are many functions within Alteryx that can be performed within the Formula Tool. We try to cover as many as possible in the form of Conditional, Conversion, Spatial, and for this article, DateTime. For any Date or DateTime field within Atleryx, you have the ability to modify or update that field directly within the Formula Tool. For example, let's say you want to find the per day average of sales of the previous month. With the DateTime functions, you can determine the last day of the previous month to find out how many days were in that month, then divide that by total sales. This is of course a simple example, but you get the point.
Moving on! You may be thinking that the built-in functions of the Formula Tool would cover something like this. In a sense, you are correct. There is a function called DateTimeLastOfMonth(), which will return the last day of the current month. The issue is that there are no additional parameters for this particular function, such as the last day of a particular month. In order to properly find the last day of any previous (or future) month, we have to nest this function within another function, DateTimeAdd(). This function allows you to add or subtract minutes, hours, days, months, or years to a given DateTime field. In our case, rather than simply stating DateTimeLastOfMonth(), we want to determine the first day of the month, minus one day. Luckily, we have another function called DateTimeFirstOfMonth() that allows us to do just that. The final result:
The result is 2013-10-31. Simple, huh? Even better, let's say we need to determine the last day from two months ago. Since we can't dynamically say -30 days, or -31 days depending on the current month, all we need to do is nest in one more DateTimeAdd() function:
The result is 2013-09-30 (if you are running the module in November). By nesting in another DateTimeAdd() function pulling the first day of the previous month minus one day, you end up with your desired result.
As previously mentioned, there are many functions within Alteryx that can be performed for a variety of reasons. The DateTime function we touch on here is just a drop in the bucket for what we have the ability to do, but is a good demonstration of some of the incredible yet simple flexibility within Alteryx.
Until next time! -Chad For more tips, tricks, and general Alteryx inquiries, follow me on Twitter! @AlteryxChad