1 Reply Latest reply on Mar 26, 2016 6:52 AM by Simon Runc

    Aggregate for < x% Market Share Part 3: The Final Level (of Detail)

    Michael Lance

      Related to this: https://community.tableau.com/message/469870?et=watches.email.thread#469870

      and this:Aggregate for < x% Market Share Part 2 (The Saga Continues)...

       

      ...I'd like to display both plan and total market shares (see attached workbook) in such a way that it:

      1. does not need to use [Hospital] for level of detail

      and

      2. displays one bar for the "Other" category

      3. Allows me to sort hospitals by total market share

       

      As a side note, which may be useful:

      I notice that when I employ the Fixed LoD method mentioned by Simon Runc in previous posts linked above (along with setting some fields as context filters), it works for one market share field but not the other.

       

      Thanks in advance!
      -Michael

        • 1. Re: Aggregate for < x% Market Share Part 3: The Final Level (of Detail)
          Simon Runc

          hi Micheal,

           

          Sorry for not getting back to you sooner on this (...been a bit of a hectic couple of weeks at the data factory!)

           

          So I think I may have caused some confusion in my previous post, by suggesting the alternative INCLUDE/EXCLUDE LoD solution. I think it's worth going through the difference between INCLUDE/EXCLUDE and FIXED LoDs...

           

          FIXED LoDs are just that, in that the Level of Detail that is used to calculate the Dimension [<6%] is FIXED to the levels in the formula. This means this calculation is immune of filtering (unless the filters are made 'in context'), and immune to the VizLoD. As such the results of a FIXED LoD are returned at Row Level (which is why this is a 'Real' dimension)...you'll notice with your INCLUDE/EXCLUDE version you are unable to drag it to the Dimension pane.

           

          INCLUDE/EXCLUDE LoDs are affected by the VizLoD and filtering, so the assignment of whether a Hospital is given it's Hospital Name or '<6%' changes depending on the filtering and VizLoD, which is why it can't be a 'real' dimension as the assignment (either 'Hospital Name' or '<6%')  is dependant on the situation (filtering/vizLoD). This is why your INCLUDE/EXCLUDE calculation (for Hospital Name/<6%) sits within the measures pane, as the result of the formula depends on how it is used (filtering/VizLoD)

           

          Hope that makes sense?

           

          So the first question is...

          Do you want a Hospital to have the same assignment (either 'Hospital Name' or '<6%') regardless of the Plan? eg. If Hospital A has an overall share of, say 10%, but for Plan A it is only 5% (and for Plan B it is 15%). Would you want Hospital A to be classified as 'Hospital A' for Plan A, and '<6%' for Plan B, or would it always be 'Hospital A'?

           

          If it's the former (although we could use a FIXED) an INCLUDE/EXCLUDE would be the best solution, but if it's the second version (every hospital is either 'Hospital Name' or '<6%' for any Plan selection) then using FIXED is the way to go.

           

          If you let me know on the above question, I'll take a look at the best solution. If you want to go for version 1, you'll need to let me know the VizLoD you intend to use as we'll need to know this to put the correct dimensions in the INCLUDE/EXCLUDE.