Updated the links to versions 1.2.1 as a file was missing from 1.2

The latest release (version 1.2) of my Alteryx formula add-ins project (now called Alteryx Abacus with thanks to Chris Love) is now available.

These are a set of utility functions for use wherever a formula can be used within Alteryx. The functions are developed in C++ and XML using the Alteryx SDK.

You can download the binaries, function reference or source code for this release from GitHub. The workflows and macros I use to test the functions can also be download and can be used to see the functions in action. Please note that you need Adam Riley’s fantastic List Runner macro (part of the CReW macros) to use the Run Unit Tests workflow although individual tests do not need it.

Please raise an issue or drop me a message if you find any problems or think of any additional functions you would like to see in a future release.

This has been tested within Alteryx 10.6. As of Beta 2 of version 11 there were some issues with custom functions in the new formula tool but I expect they will be resolved prior to the final release.

Installation

There is an Install.bat script which will copy all the XML files and the DLL files into this folder. As Windows UAC protects this folder, if it is within Program Files, the install script will ask for permission to install. The script will also unblock all files – recent versions of Windows will probably have blocked them as they were downloaded from the internet. Please note, this script requires Windows 7 or later.

If you have a non-admin install of Alteryx, it is possible the install script won’t be able to find the Alteryx folder. In this case or if the bat file fails you will need to copy the files manually.

An additional Uninstall.bat script can be used to reverse this process and remove the copied XML and DLL files. Again for admin installs this will require administrator access.

Manual Installation:

Alteryx looks for XML files in a folder called FormulaAddIn within the RuntimeData folder inside the bin directory of the Alteryx install. For an admin install, this will default to be: C:\Program Files\Alteryx\bin\RuntimeData. For a non –admin install, the default is %HOMEPATH%\AppData\Local\Alteryx\bin\RuntimeData.

All you need to do to manually install Alteryx Abacus is copy all the XML and DLL files to %AlteryxBinDirectory%\RuntimeData\FormulaAddIn (you will probably find a Sample.xml file already there). To uninstall, just delete the files you added.

Release Notes

Version 1.2

• Thanks to the Boost libraries, added Normal and Log-Normal distributions functions
• Improved error reporting back to Alteryx
• Added Deg and Rad functions

Version 1.1.1

• Adjusted compilation mode so no longer dependent on Visual C++ 2015 runtime being installed

Version 1.1

• Full set of unit tests for all the functions.
• Wiki now contains a function reference for all the functions.
• Improved Install.bat to pick up all XML and DLL files.
• New functions: AVG, COUNT, SUM, MAKETIME, MAKEDATETIME, TODATE, TODATETIME, DATEADD
• HEXBINX and HEXBINY moved to C++ functions
• Bug Fix: LEFTPART and RIGHTPART missing bracket.
• Bug Fix: RIGHTPART returns the whole string if delimiter not found.
• Bug Fix: MODULO deal with NULL or 0 for divisor.

Version 1.0

• Initial release

Function List

MiscUtils

A few additional general methods for data preparation.

• IfNull: If first value is null return second
• Coalesce: Given list of values return first non-null value

MathUtils

Math based functions that I haven’t got a better home for!

• Modulo: General Double Based Modulo function
• HexBinX: Given an X, Y point and optional radius, get X coordinate of hexagonal bin’s center
• HexBinY: Given an X, Y point and optional radius get Y coordinate of hexagonal bin’s center
• Rand_Triangular: Given a uniform random number transform into a triangular distributed random number.
• Avg: Average of a list of values ignoring NULL
• Count: Count of a list of values ignoring NULL
• Sum: Sum of a list of values ignoring NULL
• Deg: Convert radians to degrees
• Rad: Convert degrees to radians
• NormDist: Compute PDF or CDF on Normal distribution
• NormInv: Compute inverse CDF on Normal distribution
• LogNormDist: Compute PDF or CDF on Log Normal distribution
• LogNormInv: Compute inverse CDF on Log Normal distribution

DateUtils

Some additional functions for working with Dates and to a certain extent Times.

Date Time Creation Functions (Currently Just Concatenation)

• MakeDate: Create a new date from Year, Month, Day
• MakeTime: Create a new time from Hour, Minute, Second
• MakeDateTime: Create a new DateTime from Year, Month, Day, Hour, Minute, Second
• ToDate: Truncate a DateTime to a Date
• ToDateTime: Appends midnight to a Date to create a DateTime

Date Part Functions (returns numbers)

• Day: Get the day of the Month [1-31]
• Month: Gets the month number [1-12]
• Year: Gets the four digit year
• WeekDay: Gets the day of the week [Sunday (0) through to Saturday (6)]
• Quarter: Gets the quarter of the date [1-4]
• OrdinalDay: Gets the day of the year [1-366]

Period Start and End

• WeekStart: Get the first Sunday before or equal to date
• WeekEnd: Get the first Saturday after or equal to date
• MonthStart: Get the first day of the month containing the date
• MonthEnd: Get the last  day of the month containing the date
• QuarterStart: Get the first day of the quarter containing the date
• QuarterEnd: Get last day of the quarter containing the date
• YearStart: Get the first day of the year containing the date
• YearEnd: Get the last day of the year containing the date

Other Date Functions

• DateAdd: Equivalent to DateTimeAdd but returning a Date
• BusinessDays: Number of weekdays between two dates
• IsLeapYear: Is a year a leap year (takes a year not a date as an input use IsLearYear(Year([Date]))

StringUtils

A few extension methods for working with strings.

• LeftPart: Gets the text before the first instance of a separator
• RightPart: Gets the text after the first instance of a separator
• Split: Splits a string into tokens and then returns the specified instance

Retired Functions

• StartsWith: Does a string start with another
• EndsWith: Does a string end with another
• Contains: Is a string in another