Bryan Bui Posted May 1, 2023 Share Posted May 1, 2023 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] Link to comment Share on other sites More sharing options...
Gaia Paolini Posted May 3, 2023 Share Posted May 3, 2023 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? Link to comment Share on other sites More sharing options...
Bryan Bui Posted May 3, 2023 Author Share Posted May 3, 2023 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. Link to comment Share on other sites More sharing options...
Gaia Paolini Posted May 5, 2023 Share Posted May 5, 2023 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. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now