Have you ever encountered a filtering issue in Tableau that you were close to solving but just...not quite there? You may need to create a set! A few months ago, I was having a heck of a time with a Tableau visualization that I was creating for a client.
This client has members (their customers) that in turn sell to companies (the members’ customers) for which I needed to show the top five based on sales from the current year (2022) and display sales for the past few years (’20, ’21 & ’22). I created an LOD calculation to assign rank as it had to be unique to the member, their customer, and the year, but when I tried to filter by that rank, I would lose the ability to display sales for previous years (because duh, the LOD was tied to current year).
I realized this was a job for a set…I created one based on the following formula:
sum({FIXED [Year], [Member],[Company],[Sales] : sum(if [Year]=2022 then [Sales] END)})*
I was getting closer, but still, what Tableau considered to be “in” my set and “out” of it didn’t look quite right – why was it considering a company that was ranked “3” to be out of the set? It was almost as if it wasn’t taking into account the member’s name I had filtered on.
In a way, that’s what was happening. I did some searching online, and lo and behold, one of the Flerlage Twins had the answer for me - IT’S AN ORDER OF OPERATIONS THING. It was computing the set (finding the top 5) before it computed my filters (filtered by member name).
The solution is to right-click on the filter name (in this case, Member) and select “Add to context”. This gives that filtering priority over the set and then the in/out set was calculated properly.
At this point, my rank field is just performative, and I don’t need it at all. In fact, because it’s based on the 2022, you’ll notice that there is a NULL rank value for previous years, which just looks sloppy. Here’s my final result:
I’ve created a story board and published this to Tableau Public, in case it's of use to you, too!
*Because I’m publishing this in 2023 and my dummy data set doesn’t have 2023 data, I’ve hardcoded ‘2022’ in my formulas. If you visit Tableau Public and download my workbook, you’ll see I’ve created a field to display the current year, and you can change the formula to be dynamic by replacing ‘2022’ with [CurrentYear].