6 Replies Latest reply on Mar 8, 2016 1:24 PM by Simon Runc

    Aggregate for < x% Market Share Part 2 (The Saga Continues)

    Michael Lance

      Related to this question:

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

      I'd like to extend those calculated fields to look at hospital market share across departments like this:

      I tried adding department to the fixed LODs like this:

      {FIXED [Area],[Hospital],[Year],[Department]:

      SUM(IF [Plan]<>"Unintelligible" AND [Hospital]<>'NA' THEN [Patients] end)}

      /

      {FIXED [Area],[Year],[Department]:

      SUM(IF [Plan]<>"Unintelligible" AND [Hospital]<>'NA' THEN [Patients] end)}

       

      What's interesting to me is that the numerator and denominator for hospital A under Critical Care are correct, and the percent is correct, but if I select more than 1 year, plan, or area, the numerators and denominators remain correct but the percentages are incorrect.

      For example:

      Scenario 1:

      Hospital: A

      Dept: Critical Care
      Plan: AAA

      Area: 1

      Year: 2013

      Numerator: 689

      Denominator: 5,099

      % ([Market Share (Dept)] value): 13.5% (correct)

       

      Scenario 2 (select 2012 in addition to 2013):

      Hospital: A

      Dept: Critical Care
      Plan: AAA

      Area: 1

      Year: 2012 and 2013

      Numerator:788

      Denominator:8,079

      % ([Market Share (Dept)] value): 13.5% (should be 9.75%)

       

      Note:

      For 2012, hospital A in scenario 1 has less than 6% market share, so it should be categorized under "Other (<6% TMS)"

      -So I'm guessing this might be a factor

       

      Looking at scenarios 1 and 2 for hospital F (which is above 6% market share for 2012 and 2013):

      Scenario 1:

      Hospital: F

      Dept: Critical Care
      Plan: AAA

      Area: 1

      Year: 2013

      Numerator: 361

      Denominator: 5,099

      % ([Market Share (Dept)] value): 7.1% (correct)

       

      Scenario 2 (select 2012 in addition to 2013):

      Hospital: F

      Dept: Critical Care
      Plan: AAA

      Area: 1

      Year: 2012 and 2013

      Numerator: 1135

      Denominator:8,079

      % ([Market Share (Dept)] value): 33% (should be 14%)

       

       

      Am I missing something obvious?

       

      Thanks in advance!

       

      Aggregate for < x% Market Share

      @Simon Runc

        • 1. Re: Aggregate for < x% Market Share Part 2 (The Saga Continues)
          Simon Runc

          hi Michael,

           

          So just taken a quick look at this...and the issue (I think) lies in the [<6% TMS (Dept)] calculated dimension.

           

          In the [Total Market Share (Dept)] calculation

          {FIXED [Plan],[Area],[Hospital],[Year],[Department]:

          SUM(IF [Plan]<>"Unintelligible" AND [Hospital]<>'NA' THEN [Patients] end)}

          /

          {FIXED [Plan],[Area],[Year],[Department]:

          SUM(IF [Plan]<>"Unintelligible" AND [Hospital]<>'NA' THEN [Patients] end)}

           

          As you have Plan/Area/Year/Department/Hospital in the dimensionality of the LoD calculation, it means that a value is returned for each Plan/Area/Year/Department/Hospital combination, and as a FIXED LoD these results are returned to ever row. This means that each Plan/Area/Year/Department/Hospital can take a different value, and so a Hospital can get classified as both <6% and Hospital Name (for different elements). I've had a look at one of your scenarios

           

          Scenario 2 (select 2012 in addition to 2013):

          Hospital: A

          Dept: Critical Care

          Plan: AAA

          Area: 1

          Year: 2012 and 2013

          Numerator:788

          Denominator:8,079

          % ([Market Share (Dept)] value): 13.5% (should be 9.75%)

           

          and in this Hospital A is classified as Hospital A in 2013, but '<6%' in 2012...and so 99 of your hospital A patients fall into '<6%'!!

           

           

          So if you only want a Hospital to be classified as 'Hospital Name'  or '<6%' for every Plan/Area/Year/Department combination, you need to remove these levels from the LoDs that you use to generate the 'Hospital Name' or '<6%' classifications. It looks like Year is the main culprit, which is why your single year scenarios tend to be correct.

           

          Hope that makes sense?

          1 of 1 people found this helpful
          • 2. Re: Aggregate for < x% Market Share Part 2 (The Saga Continues)
            Michael Lance

            Hi Simon,

             

            Based on your feedback,I removed [Year] from the LOD in both the numerator and denominator, and based on earlier feedback, I then added [Year] as a context filter and all went well.

             

            Thanks again,

            Michael

            • 3. Re: Aggregate for < x% Market Share Part 2 (The Saga Continues)
              Michael Lance

              As an additional note, in my actual data I had a hospital that occurred in both areas (1 and 2) so I had to remove [Area] from the fixed LOD and add it as a context filter as well.

              I hope others may benefit from this as much as I have.

              • 4. Re: Aggregate for < x% Market Share Part 2 (The Saga Continues)
                Simon Runc

                Thanks for posting back...always like to see how a story ends, and glad you've found it so useful.

                 

                For the sake of completeness....I've worked up an EXCLUDE example...Although more complicated to set-up/think-through, they are filter responsive and are more efficient (if your data is quite big you'll start to notice a performance cost with all the LoDs and context filters, as they both create [most of the time] sub-queries, which have to be run)...btw don't get me wrong FIXED LoDs are fantastic and I use them all the time! (in fact this solution uses them to create the Hospital Name/'<6%' dimension) but wanted to show how useful their INCLUDE/EXCLUDE friends are too!!

                 

                So per the above I've used the FIXED we created earlier for the 'Hospital Name'/'<6%' dimension.(btw I removed [Year] so each hospital is always in the same 'Hospital Name'/'<6%' class...as a 'real' (Row Level) dimension we can access it in the EXCLUDE statement.

                 

                so Total Patients across all hospitals, that are selected would be;

                 

                { EXCLUDE [<6% TMS (Dept)]:

                SUM(IF [Plan]<>"Unintelligible" AND [Hospital]<>'NA' THEN [Patients] end)}

                 

                and so our Share of Patients is now

                 

                SUM([Patients])

                /

                SUM({ EXCLUDE [<6% TMS (Dept)]:

                SUM(IF [Plan]<>"Unintelligible" AND [Hospital]<>'NA' THEN [Patients] end)})

                 

                Hopefully this makes sense, but goes to show there is always more than one way in Tableau!!

                1 of 1 people found this helpful
                • 5. Re: Aggregate for < x% Market Share Part 2 (The Saga Continues)
                  Michael Lance

                  Interesting point, Simon.

                   

                  So are you saying that I would simply replace this:

                   

                  {FIXED [Hospital],[Department]:

                  SUM(IF [Plan]<>"Unintelligible" AND [Hospital]<>'NA' THEN [Patients] end)}

                  /

                  {FIXED [Department]:

                  SUM(IF [Plan]<>"Unintelligible" AND [Hospital]<>'NA' THEN [Patients] end)}

                   

                  with this?:

                   

                  SUM([Patients])

                  /

                  SUM({ EXCLUDE [<6% TMS (Dept)]:

                  SUM(IF [Plan]<>"Unintelligible" AND [Hospital]<>'NA' THEN [Patients] end)})

                   

                  If so, I'd probably do this, unless you think it would be counter-productive or redundant:

                   

                  SUM(IF [Plan]<>"Unintelligible" AND [Hospital]<>'NA' THEN [Patients] end)

                  /

                  SUM({ EXCLUDE [<6% TMS (Dept)]:

                  SUM(IF [Plan]<>"Unintelligible" AND [Hospital]<>'NA' THEN [Patients] end)})

                   

                  So if I have it right, I can replace what I had with the exclude function and get the same result, only more efficiently with large data sets?

                   

                  Thanks again,

                  Michael

                   

                  P.S. I may post one more related question in the next hour unless I can figure it out on my end. It's related to the first question I posted in this series.

                  • 6. Re: Aggregate for < x% Market Share Part 2 (The Saga Continues)
                    Simon Runc

                    SUM([Patients])

                    /

                    SUM({ EXCLUDE [<6% TMS (Dept)]:

                    SUM(IF [Plan]<>"Unintelligible" AND [Hospital]<>'NA' THEN [Patients] end)})

                    Pretty much!

                    I can replace what I had with the exclude function and get the same result, only more efficiently with large data sets?

                    Exactly!

                     

                    Now when you say replace it's not quite a LFL replacement as the VizLoD is now in play. Currently we only have 2 Dimensions adding to the canvas vizLoD (our 'Hospital Name'/'<6%' dimension, and 'Department'), so in one calculation we exclude the 'Hospital Name'/'<6%' dimension to get the SUM([Patients]) total sum by Department.The filter shelf doesn't add to our VizLoD...However If in another Viz you had, say, Plan in the VizLoD you'd need to add [Plan] to the Exclude Statement. With FIXED you can forget about the VizLoD....however (as we have found here) that can have it's own complications.