Dynamic Filters in Alteryx

One common task most analysts encounter is the need to setup dynamic filters in their data workflows.  In SQL this is usually accomplished via a sub-query which is joined in to a main query.  In Alteryx, where each step is accomplished via dedicated tools or nodes, the process is similar but much more visual.  Usually it involves some combination of the summarize, join, and filter tools.

Here at the TABS Group, we process lots of CPG sales data.  Sometimes it comes in a weekly format and we often apply a threshold to remove items which have little to no sales over a two year period.  Below is an example how this is easily accomplished via Alteryx.

The data begins in the select tool labeled “Starting Point” above.  Below you can see how the sales data is structured based on GEO (Geography or market where sales occurred), UPC, and Time (weekly periods).  The measure columns are Dollars, Units, and ACV.   The goal is to keep the data at a weekly level, but apply a dynamic threshold that removes any items from the stream that have sales less than $500 across all weeks.

The first step in applying the threshold is to add a summarize tool along the side of the  main data stream that sums up the dollar sales across all weeks.  You can see that in my initial screenshot labeled as “Sum 2Yr Sales”.  This tool uses a group by method for the Geography and UPC fields, and sums the dollars (Notice how we’re following traditional SQL steps without needing to actually code anything).

Once the totals are created, we join those back into the main data stream using the join tool.  Simply connect both streams to the right and left input connectors on the join tool, and configure the join using the intuitive screen below.  Notice the Venn diagrams illustrating the concept of Left, Right, and Full joins (These are new to Alteryx 9.5)   Notice also in the field section how I unchecked the Right_GEO and Right_UPC fields so as not to end up with redundant fields in the output data stream.

The end result of this node is that we now have weekly sales for each item along with a new 2 Yr Total sales column.  If I click one of the binocular icons (browser nodes) we can see what the data looks like after the join.

Now it’s just a matter of applying a simple filter to get rid of any rows where the 2YR total sales are below $500.  In alteryx 9, you can now do filters two ways.  Either use the simple basic filter option at the top (as I’ve done here) or create a more complex filter below using the custom filter options.  The nice thing is that even if you create a basic filter, Alteryx still creates the actual expression and displays it below so you can validate how it looks.

So there you have it!  I very simple example of a dynamic sales-based filter which can be applied.  Of course there are all kinds of other dynamic filters you could use, such as filtering on product categories that contain sales from certain manufacturers, etc.  It all comes down to adding simple summarize and filter nodes and tying everything together with joins.