Last Friday’s project challenged us to practice the two main topics covered during week 4, Table Calculation in Tableau and Macros in Alteryx.
The task aim was to reproduce as many table calculations as possible in Alteryx, transforming them in dynamic macros.
In this post, I would like to show the calculations I’ve managed to reproduce in the time span of 4 hours.
The first calculation I reproduced is Difference from previous/next.
This simple row-level calculation returns the previous or next value at the Lowest level of granularity in the view.
In Alteryx, this function can be simply done by using the tool multi-row formula. The macro, in fact only performs this single tool. I then added some action tools in order to let the end user select the target field and the ability to select the previous or the next value.
The second calculation I reproduced is Lookup. This function, like the previous one, returns a previous or next value, although with this function the user can specify the interval from the current row. in alteryx this function can be performed by simply tweaking the previous formula with the desired number of rows.
To create this macro I simply had to add another 2 action tools to the first macro. These action tools allows the end user to specify the number of rows interval.
The third calculation I reproduced is Percentage difference from. This macro is again based on the first one. I simply added another multi-row formula tool, in order to calculate the percentage of the value previously obtained. This formula also specifies the divisor using an IF-THEN statement.
The fourth and fifth calculation I reproduced are Running Sum/Average.This was once more obtained using a multi-row formula tool.
To calculate the running average instead, I had to add 2 extra tools. I first added a Record ID tool in order to give a Progressive number to each row in the view. After that, I added another multi-row formula tool that would divide the running sum by this number.
The latest and most articulate macro I built is Percentage of total. This function returns the percentage of the total of a certain value, where the user can specify which level of granularity to use as the total value.
To replicate these in Alteryx I had to use 2 summarize tool which would calculate the Sum of a specified measure, to be then used as a divisor in a formula tool.
The action tools allow the user to specify two level of granularity and one measure to calculate by.
During the presentation, I as well as some of my peers, realised that the tool we created only worked when connected to the same input we used to build the macros (Sample-Superstore), becoming unusable with any other input. This shows that I definitely need to practice more macros and Alteryx, in general, to be able to produce accurate workflows.
Despite this flaw, this exercise was really helpful to recap all the different table calculations. Reproducing them has helped us understand the process that Tableau runs in the background when we set a calculation on the view, as well as improving our knowledge of the functioning of Alteryx.
