Histogram Color Selected Field

[NickHoff]

Currently I'm using the following expression to color the selected bar in my histogram:

if(isnull(only($(eCostLevel))), RGB(220,220,220),RGB(141,170,203))

$(eCostLevel) is =@CostDescriptionField

When the user selects Facility @Cost Level and the range/bar the coloring works as shown:

However, if the user selects a different @CostLevel other than Facility the chart filters down to the selection.  Why is this?

The following is the field for @CostDescriptionField

IF(HCACostScore = 1, DUAL('LESS THAN 1.0%',1),

      IF(HCACostScore = 2,DUAL('1.1% to 5.0%',2),

      IF(HCACostScore = 3,DUAL('5.1% to 25.0%',3),

      IF(HCACostScore = 4, DUAL('25.1% to 50.0%',4),

      IF(HCACostScore = 5, DUAL('50.1% to 75.0%',5),

      IF(HCACostScore = 6, DUAL('75.1% to 95.0%',6),

      IF(HCACostScore = 7, DUAL('95.1% to 99.0%',7),

      IF(HCACostScore = 8, DUAL('GREATER THAN 99.1%',8))))))))) AS HCACostDescription,

     GroupCostScore,

     IF(GroupCostScore = 1, DUAL('LESS THAN 1.0%',1),

      IF(GroupCostScore = 2,DUAL('1.1% to 5.0%',2),

      IF(GroupCostScore = 3,DUAL('5.1% to 25.0%',3),

      IF(GroupCostScore = 4, DUAL('25.1% to 50.0%',4),

      IF(GroupCostScore = 5, DUAL('50.1% to 75.0%',5),

      IF(GroupCostScore = 6, DUAL('75.1% to 95.0%',6),

      IF(GroupCostScore = 7,DUAL('95.1% to 99.0%',7),

      IF(GroupCostScore = 8,DUAL('GREATER THAN 99.1%',8))))))))) AS GroupCostDescription,

     DivisionCostScore,

     IF(DivisionCostScore = 1,  DUAL('LESS THAN 1.0%',1),

      IF(DivisionCostScore = 2, DUAL('1.1% to 5.0%',2),

      IF(DivisionCostScore = 3,DUAL('5.1% to 25.0%',3),

      IF(DivisionCostScore = 4, DUAL('25.1% to 50.0%',4),

      IF(DivisionCostScore = 5, DUAL('50.1% to 75.0%',5),

      IF(DivisionCostScore = 6, DUAL('75.1% to 95.0%',6),

      IF(DivisionCostScore = 7,DUAL('95.1% to 99.0%',7),

      IF(DivisionCostScore = 8,DUAL('GREATER THAN 99.1%',8))))))))) AS DivisionCostDescription,

     MarketCostScore,

     IF(MarketCostScore = 1,  DUAL('LESS THAN 1.0%',1),

      IF(MarketCostScore = 2, DUAL('1.1% to 5.0%',2),

      IF(MarketCostScore = 3,DUAL('5.1% to 25.0%',3),

      IF(MarketCostScore = 4, DUAL('25.1% to 50.0%',4),

      IF(MarketCostScore = 5,DUAL('50.1% to 75.0%',5),

      IF(MarketCostScore = 6, DUAL('75.1% to 95.0%',6),

      IF(MarketCostScore = 7,DUAL('95.1% to 99.0%',7),

      IF(MarketCostScore = 8,DUAL('GREATER THAN 99.1%',8))))))))) AS MarketCostDescription,

     FacilityCostScore,

     IF(FacilityCostScore = 1,  DUAL('LESS THAN 1.0%',1),

      IF(FacilityCostScore = 2, DUAL('1.1% to 5.0%',2),

      IF(FacilityCostScore = 3,DUAL('5.1% to 25.0%',3),

      IF(FacilityCostScore = 4, DUAL('25.1% to 50.0%',4),

      IF(FacilityCostScore = 5,DUAL('50.1% to 75.0%',5),

      IF(FacilityCostScore = 6,DUAL('75.1% to 95.0%',6),

      IF(FacilityCostScore = 7,DUAL('95.1% to 99.0%',7),

      IF(FacilityCostScore = 8,DUAL('GREATER THAN 99.1%',8))))))))) AS FacilityCostDescription

[sunny_talwar · Accepted solution]

What is the expression you are using here? The issue seems to be not with your color expression, but your regular chart expression which is used to plot the chart. The problem seems to be related to the fact that there is no set analysis to ignore selection in @CostLevel. But then when you select Facility, the selection is somehow ignored? That seems a little strange, but it might help to know what you expression is. Also, it might even be better if you are able to share a sample for us to see the issue, because things become much easier to resolve by looking at them

Best,

Sunny

[Anonymous]

I agree with Sunny T about it being your regular chart expression that's allowing the chart to change based on selection. If you're using an aggregation expression like Sum you should write it like this: Sum({1) YourField). If you're just using a field this might work: Only({1} YourField)

Also, I'd recommend in your script using Pick and Match rather than nested If statements:

Pick(Match(HCACostScore,1,2,3,4,5,6,7,8),DUAL('LESS THAN 1.0%',1),DUAL('1.1% to 5.0%',2),DUAL('5.1% to 25.0%',3),DUAL('25.1% to 50.0%',4),DUAL('50.1% to 75.0%',5),DUAL('75.1% to 95.0%',6),DUAL('95.1% to 99.0%',7),DUAL('GREATER THAN 99.1%',8)) as HCACostDescription,

[NickHoff]

The expression for the histogram is just:

=COUNT({$<FacilityCostDescription = >}DISTINCT SurgicalCaseSk)

Depending on the selection it could be HCA/Group/Division/Market/FacilityCostDescription

[NickHoff]

Sunny,

You were correct about it being the chart expression.  My issue was I wasn't using $(eCostLevel) in my chart, instead I had FacilityCostDescription.  Developer error here.