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

Hi,

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])

image.png.04a44ec8622fec820bf26e4f8957fd3a.png 

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

Hi,

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.

image.png.138059082614ee06ce98512d6f2fd567.png 

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...