Thank you for replying.
I created a dashboard(with missing functionality), that I am looking to achieve---> the user would select a customer and Tableau identifies what segment the customer falls in and populates the right side chart with the top customer within the selected customer's segment.
I still could not figure this out.
One not so elegant way I found was to display the segment filter and hardcode the relevant segment value which will then bring up the top customer. Is there a more elegant way you would know, which doesn't need me to hardcode the segment value by clicking on the radio button?
2 of 2 people found this helpful
How about this?
The challenge is around Tableau's order of operations. Filter Actions and scoped & global filters by default are dimension filters, and we can move them to be earlier in the order of operations by turning them into context filters. So whatever calculation we are using to compute the top customer in the selected customer's segment has to have higher precedence than a dimension filter. Table calculations (like INDEX()) occur later in the order of operations so they won't work in this case. Level of Detail (LOD) expressions are useful here because a FIXED LOD expression is computed before regular dimension filters are applied.
So I set up a series of them:
Sales per Customer - gets the sales per customer
Max Sales per Segment - gets the max Sales per Customer per segment (and returns it to every row in the Segment)
Max Customer in Segment - a boolean field that flags whether a row belongs to the customer with the most sales per segment. Note that this is using ROUND() because LOD expressions are not guaranteed to return the exact same results as other aggregations due to how databases do floating point math.
Max Customer Name in Segment - returns the customer name of the customer with the most sales in the segment and returns it to every row in the segment
Then I changed the Top customer in selected customer's segment worksheet to use the new fields, and made the filter on Customer Name into a scoped filter to apply to both worksheets.
So what happens now is when you use the scoped filter it applies to the Top customer sheet and just returns the records for that customer. Since the LOD expressions are effectively computed before the filter is applied the information about the top customer for the selected customer's segment is also available as record level values and that's what gets displayed.
Thank you. This is what I was looking for. I tried INDEX, other calcs(if cust name then segment), LODs and anything that came to my mind. Among the LODs, Sales per customer and Max sales per Segment are the farthest I got when I was trying different things. While I now understand what you did with Max Customer in Segment and Max Customer Name in Segment, I will still need atleast an hour to absorb all of this
Big thanks once again for your time Jonathan.