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

I am trying to create custom expression on a line chart that allows the display of a moving average % value. The # of periods evaluated in the moving average calculation is determined by an inputted value into a document property.

Bryan Bui

Recommended Posts


I am trying to create a custom expression on a line chart that will display a moving average % value. I am able to create independent "Numerator" and "Denominator" values on the line chart, but I am unable to combine the values to create the moving average % value that I need.

Below are the elements I am working with:

$MovingAverageSelection = A document property that receives an inputted integer value by the user.

Numerator = Sum([Column A] + [Column B])

THEN Sum([Value]) OVER (LastPeriods(${MovingAverageSelection},[Axis.X]))

THEN If(Count() OVER (LastPeriods(${MovingAverageSelection},[Axis.X]))=${MovingAverageSelection},[Value],null)

Denominator = Numerator = Sum([Column A] + [Column B] + [Column C])

THEN Sum([Value]) OVER (LastPeriods(${MovingAverageSelection},[Axis.X]))

THEN If(Count() OVER (LastPeriods(${MovingAverageSelection},[Axis.X]))=${MovingAverageSelection},[Value],null)

The "Numerator" and "Denominator" custom expressions return as integer values, and essentially sum up the respective columns based on # of time periods determined by $MovingAverageSelection.

What I want is to be able to create a moving average % custom expression that appropriately sums up the numerator and denominator values before dividing.

Example below:

Sample Data Table:

image.png.e26e64ea2838ba41f2b58d857ca4c63b.png$MovingAverageSelection = 2 (Meaning that we are looking at a rolling 2 months of data)

The expected values listed per period would be as follows:

Numerator = Sum([Column A] + [Column B])

Denominator = Sum([Column A] + [Column B] + [Column C])


I have tried the following, but have ran into issues.

Sum([Column A] + [Column B])/

Sum([Column A] + [Column B] + [Column C])

THEN Avg([Value]) OVER (LastPeriods(${MovingAverageSelection},[Axis.X]))

THEN If(Count() OVER (LastPeriods(${MovingAverageSelection},[Axis.X]))=${MovingAverageSelection},[Value],null) as [Rolling Avg]

*This returns the moving average of the % value per period, rather than taking into account the the entire time frame.

I'm hoping to run something like this, but am getting an error message due to a categorical X-Axis:

Sum([Column A] + [Column B])

THEN Sum([Value]) OVER (LastPeriods(${MovingAverageSelection},[Axis.X])) 


Sum([Column A] + [Column B] + [Column C]) OVER (LastPeriods(${MovingAverageSelection},[Axis.X]))as [Rolling Avg]

Link to comment
Share on other sites


Yes, the X-axis is a date hierachy (Year -- Quarter --Month -- Week) and there is a dual Y-axis. In the screenshot below the "Numerator" and "Denominator" lines are using the secondary Y-Axis.

The data is transactional, so one or many rows per day.


Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...