It’s really nice to be reminded how simple some things can be in Tableau. Rody does a great job reminding us of that with this Workout Wednesday challenge.

The challenge was pretty simple.

• Show a table of Sales by Customers and Years
• Only Include Customers whose sales have increased each year from 2014-2017
• Sort them by their 2017 Sales amount

This should be pretty simple

1. Put year of order date on columns
2. Customer name on rows
3. Then we’ll throw sum of sales on the text mark card

Boom, first requirement done!

Now to get the list of customers down to a) only those with sales all four years and b) only those who increased their sales each year. Now, to make this a real challenge, Rody specified that I could not use any LODs or table calcs for this challenge. I could have used either of those but would’ve resulted in multiple calculations and depending on the size of the dataset, that could impact performance.

I think I can do this with a set. Sets are great. You essentially create a boolean dimension that can then be used in a multitude of ways. And for this challenge, I want to create a set that checks each customer to the second bullet in the requirements above. So I think I’m going to need to create separate measures that sums up each the sales for each year. The basic syntax for each of these calcs is:

SUM(IF YEAR([Order Date])=XXXX then [Sales] end)

I chose to hardcode the aggregation. Then I’ll replace the four-digit year for XXXX. This results in four calc fields, one for each year and now I’m ready to create my set.

To create a set, I’m going to right-click on customer name -> create -> Set

When the dialog box pops up, click on the middle tab called “Condition.” From here we have a couple options. We could set a threshold by field but for this challenge, we’re going to use a formula. We want to Tableau to check, for each customer that their sales for a given year were more than that of the previous year. So in Tableau syntax, our calculation will look like this.

[2015 sales]>[2014 sales]
and
[2016 sales]>[2015 sales]
and
[2017 sales]>[2016 sales]
and
[2014 sales]>0

Each line is a separate boolean argument, resulting in one overarching boolean and that will satisfy the requirements for the creation of a set.

Now, we just need to put our new set pill onto the filter card and we’re all done! Click the screenshot for the interactive version and to download the workbook for yourself.

Sets are great because this creates a dimension that could easily be used in additional calculations and/or analysis and you can even combine sets to show shared members of multiple sets and more! If you have any boolean calculated fields in your IRL data, you might look at creating sets in the future.

Until next time!