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

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

Related to this question:

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?

Aggregate for < x% Market Share

@Simon Runc

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

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)

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)

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)

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)

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)

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.