Market Share Variance in a Custom Expression

[Sally Lee]

Good morning,

I have a cross table with encounters as the value. Year in columns, and hospitals in rows.

2016 2020

Hospital A 30 40

Hospital B 70 80

Total 100 120

I have a custom expression that calculates the market share for each year:

Sum(If([Calendar_Year]="2016",[Encounters] )) THEN [Value] / Sum([Value]) OVER (All([Axis.Rows])),

Sum(If([Calendar_Year]="2020",[Encounters] )) THEN [Value] / Sum([Value]) OVER (All([Axis.Rows]))

Result is:

2016 2020

Hospital A 30% 33%

Hospital B 70% 67%

Total 100% 100%

The problem is calculating the change in market share from 2016 to 2020 (hospital A result would be +3%; hospital B would be -3%; total = 0%). Any suggestions I have tried :

Sum(If([Calendar_Year]="2020",[Encounters] )) THEN [Value] / Sum([Value]) OVER (All([Axis.Rows])) - Sum(If([Calendar_Year]="2016",[Encounters] )) THEN [Value] / Sum([Value]) OVER (All([Axis.Rows]))

This returns an invalid expression. I would appreciate any suggestions or ideas you may have. I am a newbie to Tibco. Thank you.