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]

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:

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

[Gaia Paolini]

Could you elaborate: what are the axes of your line chart and how do the expressions for Numerator and Denominator enter it? Does your data have one row per month?

[Bryan Bui]

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.

 

[Gaia Paolini]

Thanks. Your setup seems more complex than the sample file might suggest. I cannot understand it without a representative sample dxp. Maybe somebody else will be able to help you.