4 Replies Latest reply on Jan 25, 2019 2:04 PM by Joe Oppelt

    Ranking by Customer Group within selected time period, regardless of other filters


      Hi there,


      I have attached a very much simplified version of what I'm attempting to build. As the title says, I'm looking to build a rank for "Segment" so that when a specific customer is selected in the bottom left "detail" section, that customer's segment rank will update from


      Rank: N/A (when more than 1 customer segment is selected or the default)




      Rank: 2 of 3 (if the segment this customer is in has the second most sales in the selected time period of the 3 segments)


      The user will either use the three filters at the top right, or the action built into the Detail section in the bottom left.


      This is the general idea, although instead of segments, I'm dealing with nearly 30,000 customer groups. When a specific customer is selected, I want to see, in the selected time period, how that customer's group is ranked: 174 of 30,000 for example.


      Any ideas? The rank of segment is currently just a text box in the attachment...I've tried multiple things I've seen in the forums and the closest I have been able to get is having a separate filter. This isn't ideal for my user.


      Thanks in advance,



        • 1. Re: Ranking by Customer Group within selected time period, regardless of other filters
          Joe Oppelt

          To get a ranking you need to use a table calc.  See the attached, [Rank Segments].


          And to make a table calc work, you have to have the whole table in there, else the values get reevaluated as the data changes.


          In Sheet8 in the attached I retained your Segment filter.  If you filter using that, you'll see the rank values on the bar chart change.  Go back to ALL in that filter.  Below it is a filter built on a table calc.  When you use a table calc as a filter, it does not filter out rows in the underlying table.  It just controls what part of the table gets displayed.  Filter out Corporate using the second filter, and you'll see that the other two segments keep their original values of 1 and 3.


          Also in this sheet I made a calc called [Segments in the data].  It's a FIXED LOD.  A FIXED LOD evaluates before any filtering occurs in the sheet, so no matter what else you filter out, you get the value as it exists in the data source.  So if you filter out something from your original Segment filter, the value displayed in the title remains at 3.


          Now you can use these two principles to display the overall rank of a segment and the total count in the data.  Corporate would show "2 of 3" if you format it that way.


          There is one caveat, though.  You can do "apply to" with a table calc filter.  You may need to change your approach to user-defined selections of criteria if you need this filter to apply to multiple sheets.  We can disc uss that if needed.

          • 2. Re: Ranking by Customer Group within selected time period, regardless of other filters



            Thank you for taking the time to work through this with me. I have tried this solution and it didn't work for me since I do have multiple sheets. My apologies, I should have initially been more specific in what I have already tried. Ideally, what the user selected on their primary filter (or even with an action) would then cause that second filter (the agg one) to automatically match that behind the scenes. I could never figure a way out how to do that.


            I also tried the "segments in the data" part, but this didn't work for me because it was giving me all-time count of my data's "segments" which are over 40k when we have had many "segments" that are no longer active so to say, so that I really only want to count the 27k "segments" in the given time period. I tried adding my order date to context and while this then gave the correct 27k "segments" (really member rollups), it also messed up the remaining LODs on my dashboard - such as the 1st invoice date for the view (regardless of order date filter) and also messed up the count of new customers/segments. I wanted these to ignore the context change but couldn't do that.


            I also used rank and size to get to this sheet below in my actual workbook. I really want the rank summary on the dashboard to either display "N/A" if more than one Member Rollup is selected or the rank shown below (1 of 27,225, etc) if a specific one is selected. Sort of like the calc for the Ship Mode Summary box on the mockup workbook I initially attached here. And for clarification, I want this ranking to change as user filters the order/invoice date, if necessary.


            • 3. Re: Ranking by Customer Group within selected time period, regardless of other filters
              Joe Oppelt

              You can pare down what the FIXED LOD counts in that calc.  You can specify dimensions so that you get a unique count for each combo of the listed dimensions.


              { FIXED [State], [Gender], [Year] : COUNTD( [something] ) }


              That would give you the count for each combination of State, Gender and Year.  The value for that combination would appear on each row of a given combination.  You can also add IF logic inside the COUNTD parentheses.


              { FIXED [State], [Gender], [Year] : COUNTD( IF [Active Flag] = 1 then [something] END ) }


              And you can make a dimensional calc and add that to the list of dimensions before the colon.


              // [Count this guy?]

              IF [First Name] = "Joe" then 1 else 0 END


              Then you would put [Count this guy] in the list, and all the "Joe"s would be counted together.


              Lots of flexibility there.


              More in my next reply.

              • 4. Re: Ranking by Customer Group within selected time period, regardless of other filters
                Joe Oppelt

                If you want to retain ranking positions yet remove dimensional values that would affect the rankings, ... that's what I tried to address initially.  Ranking can only be determined when you have all the values to compare them against each other.


                An ugly alternative is to do some ETL to your data before pulling it into Tableau, and setting the rank values ahead of time in the data source itself.  Then you could filter out anything you want, and "Corporate" would still have its rank value as a field within the rows already.  (Last resort on that one...)


                Traditionally the way to deal with this is to "build" your filtering approach with parameters.  Make a Start Date and End Date parameter to determine date ranges.  Make a [State Param] parameter that is populated by the [State] field from the data source.  Etc.  Problem with parameters is that they are only and always single-select.  If you want the user to select any number of states from the [State Param] list, that won't work.  Further, if the param is based on a data field that is constantly changing, it doesn't update its list dynamically when the data changes.


                But if params can work, you can build table calc filters that leverage the param values, and those calcs can go on FILTERS on each sheet and work uniformly across the sheets.


                You MIGHT be able to do something with Set Actions.  It's a new feature.   (2018.2?  Or .3?)   It's designed that you can make it work like a parameter, and it can be multi-select, and it will update dynamically with the data.  There are how-to videos and lots of KB articles on them.   I haven't played with them enough to guide you in any meaningful way though.