Abacus Version 1 3

Alteryx Abacus Version 1.3

Happy to announce the release of version 1.3 of the Alteryx Abacus functions. This release adds some new functions, corrects a few issues (particularly around installation). The full release notes are on the project's wiki.

For the first time, I have had the opportunity to test the functions within an Alteryx Server instance (version 11.3 running based off the Azure Marketplace instance) and found the functions worked perfectly - just follow the instructions below within the desktop on the server.

Downloads

As always the latest release and source code can be found on the project's GitHub page. The links below are for the 1.3 release:

• Compiled AddIn
• Function Reference
• Unit Test Workflows
• Full Source Code

Installation

Requires Power Shell v3 or Later (built into Windows 8 onwards)

Download the compiled add-in and extract all the files to a temporary location

There is an Install.bat script included with the add-ins which will copy all the XML files and the DLL files into correct folder needed by Alteryx. If you have Windows 10, you may need to approve the script past Windows Smart Screen:

As Alteryx may have been installed within a folder protected by UAC (for example within Program Files), the install script will ask for permission to install. The script will also unblock all files, as Windows will possibly have blocked them as they were downloaded from the internet.

For Windows 7 Users: There is another script Install Win7.bat which performs the same process but doesn't rely on PowerShell. While I believe this script works just as well, the PowerShell script has been tested more than this new script.

Once installed you can use the Unit Test workflows to verify it is all working. Download the zip file and extract. If you have the CReW Macros installed then you can use the RunUnitTests.yxmd workflow to run all the tests.

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 typically be within Program Files (for example C:\Program Files\Alteryx\bin\RuntimeData\FormulaAddIn).

If you cannot run as an administrator and need to manually install then copy all the XML and DLL files from the compiled zip to %AlteryxBinDirectory%\RuntimeData\FormulaAddIn.

Uninstalling The AddIn

To uninstall just run Uninstall.bat (or Uninstall Win7.bat). This script will remove all files from version 1.3 or older versions.

New Functions

DateFromMDY

Syntax: DATEFROMMDY(MDYString)

Examples:

• DATEFROMMDY('7/2/17') returns 2017-02-07

Parse a string in Month Day Year format to a Date (it can cope without leading 0s and different separators)

Thanks to Ken Black for this idea

DateFromDMY

Syntax: DATEFROMDMY(DMYString)

Examples:

• DATEFROMDMY('7/2/17') returns 2017-07-02

Parse a string in Day Month Year format to a Date (it can cope without leading 0s and different separators)

MakeDate

C++ Function - MakeDate

Syntax: MAKEDATE(Year, Month, Day) Defaults: Month = 1, Day = 1

Examples:

• MAKEDATE(2010, 1, 2) returns '2010-01-02'

Creates a date from numerical inputs of Year, Month and Day. Month and Day are optional inputs defaulting to 1.

• As of version 1.3, this checks the validity of the inputs and will warn if invalid. Please note that 0, 0, 0 is a valid input (i.e. no warning message) returning NULL.

MakeTime

C++ Function - MakeTime

Added to Time category

Syntax: MAKETIME(Hour, Minute, Second) Defaults: Hour = 0, Minute = 0, Second = 0

Examples:

• MAKETIME(12, 34, 56) returns '12:34:56'

Creates a time from numerical inputs of Hour, Minute and Second. All arguments are optional, defaulting to 0.

• As of version 1.3, this checks the validity of the inputs and will warn if invalid.

MakeDateTime

C++ Function - MakeDateTime

Added to DateTime category

Syntax: MAKEDATETIME(Year, Month, Day, Hour, Minute, Second) Defaults: Month = 1, Day = 1, Hour = 0, Minute = 0, Second = 0

Examples:

• MAKEDATETIME(2010, 1, 2, 12, 34, 56) returns '2010-01-02 12:34:56'

Creates a date and time from numerical inputs of Year, Month, Day, Hour, Minute and Second. All arguments except Year are optional.

• As of version 1.3, this checks the validity of the inputs and will warn if invalid.

TDist

C++ Function - TDist

Syntax: TDIST(X, DegreesOfFreedom)

Computes the two-tailed probability on a student T distribution with specified degrees of freedom for a value of x.

TInv

C++ Function - TInv

Syntax: TINV(P, DegreesOfFreedom)

Computes the inverse of cumulative distribution function value for a specified value of P on the two tailed student T distribution with specified degrees of freedom.

ChiDist

C++ Function - ChiDist

Syntax: CHIDIST(X, DegreesOfFreedom)

Computes the right-tailed probability on a Chi-Squared distribution with specified degrees of freedom for a value of x.

ChiInv

C++ Function - ChiInv

Syntax: CHIINV(P, DegreesOfFreedom)

Computes the inverse of the right-tailed probability for a specified value of P on the Chi-Squared distribution with specified degrees of freedom.

ToRoman

Syntax: TOROMAN(Value)

Examples:

• TOROMAN(10) returns X

Returns the Roman Numeral representation of a number. Valid inputs from 1 to 5000.

Thanks to Ken Black for this idea

FromRoman

Syntax: FROMROMAN(RomanNumerals)

Examples:

• FROMROMAN('X') returns 10

Returns numeric value of a Roman Numeral. Only tested to 5000, but should cope with a longer list of Ms!

Thanks to Ken Black for this idea

Version

Added to Specialized category

Syntax: VERSION()

Examples:

• Version() returns 11.0 (assuming running in 11.0!)
• Version(3, ) returns the Build and Revision number

Gets the Major and Minor version numbers of the Engine as a number. Other parts of the version can be specified by passing arguments to the function.

This allows for version dependent function within the formula tools. The constant is not available at parse time.

ReportError

Added to Specialized category

Syntax: ReportError(Condition, Message, ReturnValue) Defaults: Condition = True, Message = "Reporting An Error!", ReturnValue = NULL

Examples:

• ReportError() returns an error of Reporting An Error!
• ReportError(0) returns NULL
• ReportError(1, 'An Error') returns an error of An Error
• ReportError(0, 'An Error', 3) returns 3
• ReportError(0, 'An Error', 'All Ok!') returns All Ok!

Allows you report an error from a function or pass through a value.

Note version 11.5 has a Message function allowing any type of message from functions - thanks to Ned :)