Aggregate for < x% Market Share Part 2 (The Saga Continues)
Michael Lance Mar 8, 2016 7:48 AMRelated 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

Market Share Question H 2.twbx 39.9 KB

H Question 2.xlsx 17.4 KB