Introducing the all-new TIBCO Community site!

For current users, please click "Sign In" to reset your password and access the enhanced features. If you're a first-time visitor, we extend a warm welcome—click "Sign Up" to become a part of the TIBCO Community!

If you're seeking alternative community sites, explore ibi, Jaspersoft, and Spotfire.

Jump to content

Market Share Variance in a Custom Expression

Sally Lee

Recommended Posts

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.

Link to comment
Share on other sites

  • Create New...