Overview

An Overview of the Alteryx Abacus Add In

The Abacus add in is a collection of custom functions that make writing expressions in Alteryx easier or gives completely new functionality (such as variables). Originally created to make my life easier, they have grown into a powerful extension for Alteryx that can speed up creation of complicated formulae. They focus in on a few key themes:

• Date and Time
• Probability Distributions
• Data Generation
• General Utility
• Variables (new in v1.4)

In general, the functions have come either from my own wants for easier ways to do things or more commonly from ideas when answering questions in the community. This document gives an overview of the collection as it stands at version 1.4 (which should be released very soon).

Please always remember when using any SDK tool or custom function in a workflow, that the add in will then need to be installed on any machine you wish to run the workflow on.

Installation

The best way to install the add in is to use the Analytic App Installer. This app should download from GitHub, extract and install the add in into Alteryx. If it doesn't work, you can get the direct download following the instructions below.

The newest release is always available from GitHub. In each release you will find the following kind of structure:

The two zip files contain the actual add in (AlteryxAbacus.v.1.3.0.0.zip) and the set of test workflows (AlteryxAbacus.v.1.3.0.0.Tests.zip). The Manual.pdf contains a snapshot of the documentation wiki produced when the release was packaged. Finally, the source code is included as both a zip and a tar.gz file.

To install onto Alteryx Designer (or Server), you just need to download the main zip file. You can then extract it and run Install.bat. This should install the functions into Alteryx. To uninstall, run Uninstall.bat. These scripts have been tested on Windows 10 but will need administrator access to run. There is also a couple of Windows 7 compatible scripts (Install Win7.bat, Uninstall Win7.bat) included in the zip file.

Date and Time

These functions make working with and manipulating dates easier. They break into a couple of collections:

DatePart: Gets part of a date or time

These return the specified part as a number. Some of the functions now have built in equivalents.

• DatePart(interval, dt): Replicates the SQL DatePart function, getting a specified part of the datetime input
• Century(dt): Gets the century for a date
• Year(dt): Gets the four-digit year
• Quarter(dt): Gets the quarter of the date [1-4]
• Month(dt): Gets the month number for a date or datetime [1-12]
• Day(dt): Get the day of the month for a date or datetime [1-31]
• OrdinalDay(dt): Gets the day of the year [1-366]
• WeekNum(dt): Gets the week number of a Datetime, with Sunday as start of week and January 1st in Week 1 [1-53]
• Weekday(dt): Gets the day of the week [Sunday (0) through to Saturday (6)]
• Hour(dt): Get the hour part of a DateTime or Time [0-23]
• Minute(dt): Gets the minute part of a DateTime or Time [0-59]
• Second(dt): Gets the second part of a DateTime or Time [0-59]

Period Start and End: Gets the beginning or end of specified periods

It is often useful to be able to round a date down or up to the period start or end. These functions provide that capabilities:

• YearStart(dt): Get First Day of Year
• YearEnd(dt): Get Last Day of Year
• QuarterStart(dt): Get First Day of Quarter
• QuarterEnd(dt): Get Last Day of Quarter
• MonthStart(dt): Get First Day of Month
• MonthEnd(dt): Get Last Day of Month
• WeekStart(dt): Get first Sunday before or equal to date
• WeekEnd(dt): Get first Saturday after or equal to date

Parse and Create: shorthand to make dates from strings or numbers

These are some quick functions for building dates either from strings, numbers or other datetimes:

• DateFromDMY(DMY): Parse a string in Day Month Year format to a Date (copes without leading 0s and different separators)
• DateFromMDY(MDY): Parse a string in Month Day Year format to a Date (copes without leading 0s and different separators)
• MakeDate(Year, Month, Day): Create a new date from Year, Month, Day
• MakeDateTime(Year, Month, Day, Hour, Minute, Second): Create a new DateTime from Year, Month, Day, Hour, Minute, Second
• MakeTime(Hour, Minute, Second): Create a new time from Hour, Minute, Second
• ToDate(dt): Truncate a DateTime to a Date
• ToDateTime(dt): Appends midnight to a Date to create a DateTime
• ToTime(dt): Get the time from a DateTime, Date (defaults to 00:00:00) or a Time.

Other Date Functions: business days and other bits and bobs

Last few functions handle working day calculations and any other miscellaneous date tools I needed:

• DateAdd(Date,i,u): Equivalent to DateTimeAdd but returning a Date
• BusinessDays(StartDate, EndDate): Number of weekdays between two dates
• Workday(dt,days): Add or remove a specified number of weekdays to a date (similar to Excel's Workday function but no support for holidays)
• IsLeapYear(Year): Is a year a leap year (takes a year not a date as an input use IsLearYear(Year([Date]))

Probability Distributions

Based on the Boost library, these functions allow computation of values on various probability curves:

• ChiDist(X, DegreesOfFreedom): Compute CDF on Chi Squared distribution
• ChiInv(P, DegreesOfFreedom): Compute inverse CDF on Chi Squared distribution
• LogNormDist(X, Location, Scale, Cuml): Compute PDF or CDF on Log Normal distribution
• LogNormInv(P, Location, Scale): Compute inverse CDF on Log Normal distribution
• NormDist(X, Mean, StDev, Cuml): Compute PDF or CDF on Normal distribution
• NormInv(X, Mean, StDev): Compute inverse CDF on Normal distribution
• TDist(X, DegreesOfFreedom): Compute two tailed Student T distribution
• TInv(X, DegreesOfFreedom): Compute inverse two tailed Student T distribution

Data Generation

General Utility

These functions don't really fit in any other category.

Geometry

• Deg(Radians): Convert radians to degrees
• Rad(Degrees): Convert degrees to radians
• HexBinX(X, Y, R): Given an X,Y point and optional radius, get X co-ordinate of hexagonal bin's centre
• HexBinY(X, Y, R): Given an X,Y point and optional radius get Y co-ordinate of hexagonal bin's centre

Variables

An Overview of the Code Behind

All of this project is open source and if you would like to contribute, I would value the submission. All of the code is open source and available on GitHub, below is a quick overview of the code and set up I use to develop the functions.

These functions are built on top the Custom Function SDK. There are two ways to create a new function - either in XML or in C++. The code is structured as follows:

├── AlteryxAbacus
│   ├── AlteryxAbacus.aps
│   ├── AlteryxAbacus.cpp
│   ├── AlteryxAbacus.h
│   ├── AlteryxAbacus.rc
│   ├── AlteryxAbacusUtils.cpp
│   ├── AlteryxAbacusUtils.h
│   ├── AlteryxAbacus.vcxproj
│   ├── AlteryxAbacus.vcxproj.user
│   ├── ChiSquaredDistribution.cpp
│   ├── DateTimeFunctions.cpp
│   ├── EngineVersion.cpp
│   ├── EngineVersion.h
│   ├── Generator.cpp
│   ├── HexBins.cpp
│   ├── LogNormalDistribution.cpp
│   ├── NormalDistribution.cpp
│   ├── resource.h
│   ├── RomanNumerals.cpp
│   ├── stdafx.cpp
│   ├── stdafx.h
│   └── StudentTDistribution.cpp
├── AlteryxAbacus.sln
├── AlteryxAbacus.sln.DotSettings.user
├── DateUtils.xml
├── MathUtils.xml
├── MiscUtils.xml
└── StringUtils.xml

The XML files contain the function definitions (both for the macro functions and the C++ based ones). They are separated by category. The C++ code is all contained in a single Visual Studio project. In general, I again keep each set of functionalities in its own cpp file making them easier to handle.

In terms of tooling to work on these, you will need a reasonable text editor (I would recommend Visual Studio Code) and a copy of Visual Studio to work on the C++ code (Community edition is fine). In addition, I use the boost library (currently I use v1.67) which you will need to download and reference for the C++ code.

Testing

Testing is crucial when creating functions. I use the CReW macros to provide the List Runner macro. The following scripts and macros are in the repository to allow local testing:

├── Install - Debug.bat
├── Install - Release.bat
├── LinkDebug.ps1
├── CreateSet.png
├── CreateSet.yxmc
├── ResultCompare.yxmc
├── RunUnitTests.ps1
└── RunUnitTests.yxmd

The Install - Debug.bat, Install - Release.bat and LinkDebug.ps1 will either install the output of a debug or release build into Alteryx. The LinkDebug script establishes a symbolic link allowing quick modification while developing. The RunUnitTests.ps1 script will run the RunUnitTests.yxmd workflow in all installed instances using the AlteryxEngineCmd.exe. This workflow seeks all workflows in folders ending with .Test in the folder name. In general, I keep a test workflow for each function in a folder named after the XML file containing the functions. For example, to test the StringUtils:

├── StringUtils.Test
│   ├── ContainsTest.yxmd
│   ├── EndsWithTest.yxmd
│   ├── FindStringLastTest.bak
│   ├── FindStringLastTest.yxmd
│   ├── FromRomanTest.yxmd
│   ├── LeftPartTest.yxmd
│   ├── RightPartTest.yxmd
│   ├── SplitTest.yxmd
│   ├── StartsWithTest.yxmd
│   └── ToRomanTest.yxmd
├── StringUtils.xml

Documentation and Test Coverage

A new addition in version 1.4 is a new workflow Coverage.yxmd which performs various static checks on the library. It looks for the following issues:

• The name is in upper case
• The text inserted matches the name of the function
• If an XML function, fixed number of parameters and that all are used
• If a C++ function, check entry point matches name
• A test workflow for the function exists
• An entry exists in the function list
• A documentation entry has been created in the wiki

This workflow uses a lot of convention to ensure that the add in is well tested and documented. By using this technique, I have found various issues that I had missed in previous versions. While I expect some work would be needed, the Coverage.yxmd workflow could easily be adjusted to check any custom function add in.

Packaging

The last of the project consists of tools for packaging and releasing:

├── CreateRelease.ps1
└── vswhere.exe

These scripts are responsible for creating a new release package. This will locate Visual Studio and run a build of the C++ code in Release mode, before using the local testing set up to install and run the unit tests. Assuming this is successful, it will then create a zip file containing all the XML files and the compiled DLL file and the following installation scripts:

├── Install.bat
├── Installer.ps1
├── Install Win7.bat
├── Uninstall.bat
├── Uninstaller.ps1
├── Uninstall Win7.bat

These scripts allow for manual installation if the analytic app fails. The final steps of creating a release is to package up the test workflows into their own zip file and to create a PDF document based on the wiki pages (this is done using Pandoc).

Wrapping Up

Hopefully these functions are helpful, and the notes here give you some details on how to use them and how they have been built and tested. If you got any issues or questions reach out to me on the community or raise an issue on GitHub.

Likewise, if you would like to contribute with new functions or code corrections, I will welcome the Pull Requests!