
Re: Color the mean of a bar chart
Ajay Krishnan Prabhakaran Aug 18, 2014 5:39 PM (in response to Nick Hoff)If you put eInitialMean in a text box what value do you get. Do you get 24?

Re: Color the mean of a bar chart
jagan mohan rao appala Aug 18, 2014 11:00 PM (in response to Nick Hoff)Hi Nick,
Try like this, for chart expression you have use [] not $,
=IF([eInitialPainScore] = eInitialMean, Black())
Hope this helps you.
Regards,
Jagan.

Re: Color the mean of a bar chart
Rob Wunderlich Aug 18, 2014 11:38 PM (in response to Nick Hoff)I believe the problem is you are calculating mean at the dimension level. Which means Score will always equal Mean. What I understand you want to do is calculate the mean for the entire set, which would be:
SUM({$<InitialIND = {'1'}>}TOTAL Triggers)/COUNT({$<InitialIND = {'1'}>}TOTAL DISTINCT(PainScore))
The TOTAL Qualifier causes the dimension to be ignored.
Since you appear to be calculating this in a variable, you could also calculate this once as a total mean by adding an "=" within the variable definition and leaving off the TOTAL:
=SUM({$<InitialIND = {'1'}>}Triggers)/COUNT({$<InitialIND = {'1'}>}DISTINCT(PainScore))
Even with either of the above, I don't think you are going to get a bar colored because you will never get an exact match between the Dimensional Score and the overall mean. So I define eInitialMean as:
=median(aggr(SUM({$<InitialIND = {'1'}>}Triggers)/COUNT({$<InitialIND = {'1'}>}DISTINCT(PainScore)),yourDimension))
Rob

Re: Color the mean of a bar chart
Nick Hoff Aug 19, 2014 12:39 PM (in response to Rob Wunderlich )This helped me get to where I was going. Instead of the =median i used AVG, and I added ROUND to the expression for eInitialMean.
Also, inside of how I previously had the background color expression. I used the TOTAL as you suggested i.e. IF (PainScore=AVG({$<$(eInitialMean)={">=0"}>}TOTAL $(eInitialMean)),BLACK())
Thanks Rob

Re: Color the mean of a bar chart
Nick Hoff Nov 25, 2014 11:26 AM (in response to Rob Wunderlich )Hey Rob,
It was a pleasure to meet you at the conference. We had a moment at lunch to discuss dimensionality to prevent the work around of stacking pivot tables and creating custom text boxes. I'm not sure how else to get a hold of you so i'm trying the community site.
I've been playing with dimensionaltity and it doesn't seem possible to do what i'm trying to accomplish. I took some screen shots. Below is the overview of the finished application. This application uses an alternate state, which is what the BenchMark in the sets refers to.
My pivot table utilizes the following pick statements
Next the three level dimensions are the following:
The metrics are all similar and uses a pick statement like the following:
Now the tricky part is the total columns, which in order to get it to work I had to create a second pivot table. With only two dimensions of IPMedicalSurgicalIND and ObsFactorsStratified, and to create the total columns I had to use the following 4 expressions.
PICK(MSPickID,
SUM({BenchMark<BenchmarkPatTypeHierarchy={'Observation','Emergency'},BenchmarkMidnightClassification = {'Two Midnights'}>}TOTAL BenchmarkHybridNetRevAmt)/SUM({BenchMark<BenchmarkPatTypeHierarchy={'Observation','Emergency'},BenchmarkMidnightClassification = {'Two Midnights'}>}TOTAL BenchmarkCases),
SUM({BenchMark<BenchmarkPatTypeHierarchy={'Surgery'},BenchmarkMidnightClassification = {'Two Midnights'}>}TOTAL BenchmarkHybridNetRevAmt)/SUM({BenchMark<BenchmarkPatTypeHierarchy={'Surgery'},BenchmarkMidnightClassification = {'Two Midnights'}>}TOTAL BenchmarkCases)
)
PICK(MSPickID,
SUM({BenchMark<BenchmarkMidnightClassification = {'At Risk One Day Stays'}, BenchmarkIPMedicalSurgicalIND = {'M'}>}BenchmarkCases) * [OBS Rate],
SUM({BenchMark<BenchmarkMidnightClassification = {'At Risk One Day Stays'}, BenchmarkIPMedicalSurgicalIND = {'S'}>}BenchmarkCases) * [OBS Rate])
PICK(MSPickID,
SUM({BenchMark<BenchmarkMidnightClassification = {'At Risk One Day Stays'}, BenchmarkIPMedicalSurgicalIND = {'M'}>}BenchmarkHybridNetRevAmt)  [Estimated Gain from One Day Stay Conversion to OP],
SUM({BenchMark<BenchmarkMidnightClassification = {'At Risk One Day Stays'}, BenchmarkIPMedicalSurgicalIND = {'S'}>}BenchmarkHybridNetRevAmt)  [Estimated Gain from One Day Stay Conversion to OP])
[Net Loss on IP One Day Stays Converting to OBS]*2
Since I used a PICK statement I can't get a grand total and had to create individual text boxes for the totals using the following:
=NUM($(eLossOneObs)
/
SUM({BenchMark<BenchmarkIPMedicalSurgicalIND={M,S},@BenchmarkInpatientIND={1}, BenchmarkMidnightClassification = {'At Risk One Day Stays'}>}BenchmarkCases),'$#,##0')
variable definition for eLossOneObs:
NUM(SUM({BenchMark<BenchmarkMidnightClassification = {'At Risk One Day Stays'}, BenchmarkIPMedicalSurgicalIND = {'M'}>}BenchmarkCases) * SUM({BenchMark<BenchmarkPatTypeHierarchy={'Observation','Emergency'},BenchmarkMidnightClassification = {'Two Midnights'}>}TOTAL BenchmarkHybridNetRevAmt)/SUM({BenchMark<BenchmarkPatTypeHierarchy={'Observation','Emergency'},BenchmarkMidnightClassification = {'Two Midnights'}>}TOTAL BenchmarkCases)
+
SUM({BenchMark<BenchmarkMidnightClassification = {'At Risk One Day Stays'}, BenchmarkIPMedicalSurgicalIND = {'S'}>}BenchmarkCases) * SUM({BenchMark<BenchmarkPatTypeHierarchy={'Surgery'},BenchmarkMidnightClassification = {'Two Midnights'}>}TOTAL BenchmarkHybridNetRevAmt)/SUM({BenchMark<BenchmarkPatTypeHierarchy={'Surgery'},BenchmarkMidnightClassification = {'Two Midnights'}>}TOTAL BenchmarkCases),'$#,##0')
Second total text box:
=NUM(SUM({BenchMark<BenchmarkMidnightClassification = {'At Risk One Day Stays'}, BenchmarkIPMedicalSurgicalIND = {'M'}>}BenchmarkCases) * SUM({BenchMark<BenchmarkPatTypeHierarchy={'Observation','Emergency'},BenchmarkMidnightClassification = {'Two Midnights'}>}TOTAL BenchmarkHybridNetRevAmt)/SUM({BenchMark<BenchmarkPatTypeHierarchy={'Observation','Emergency'},BenchmarkMidnightClassification = {'Two Midnights'}>}TOTAL BenchmarkCases)
+
SUM({BenchMark<BenchmarkMidnightClassification = {'At Risk One Day Stays'}, BenchmarkIPMedicalSurgicalIND = {'S'}>}BenchmarkCases) * SUM({BenchMark<BenchmarkPatTypeHierarchy={'Surgery'},BenchmarkMidnightClassification = {'Two Midnights'}>}TOTAL BenchmarkHybridNetRevAmt)/SUM({BenchMark<BenchmarkPatTypeHierarchy={'Surgery'},BenchmarkMidnightClassification = {'Two Midnights'}>}TOTAL BenchmarkCases),'$#,##0')
Third total text box:
=NUM(SUM({BenchMark<BenchmarkIPMedicalSurgicalIND={M,S},@BenchmarkInpatientIND={1},BenchmarkMidnightClassification = {'At Risk One Day Stays'}>} BenchmarkHybridNetRevAmt)$(eLossOneObs),'$#,##0')
Forth total text box:
=NUM(SUM({BenchMark<BenchmarkIPMedicalSurgicalIND={M,S},@BenchmarkInpatientIND={1},BenchmarkMidnightClassification = {'At Risk One Day Stays'}>} BenchmarkHybridNetRevAmt * 2)$(eLossOneObs) *2,'$#,##0')
