Jump to content
We've recently updated our Privacy Statement, available here. ×

How to calculate percentage difference of two moving averages calculated using custom expression in a cross tab


Mohd Zoheb Salim

Recommended Posts

I have a data set which has one date column and two numerical columns. I need to calculate 7 day moving average and their respective percentage difference in across tab which should be dynamic (respond to filters). I am able to calculate moving average using below formula but not able to get the correct percentage difference. Sum([col1]) THEN Avg([Value]) OVER (LastPeriods(7,[Axis.Rows])) THEN If(Count() OVER (LastPeriods(7,[Axis.Rows]))=7,[Value],null) as [mavg1], Sum([col2]) THEN Avg([Value]) OVER (LastPeriods(7,[Axis.Rows])) THEN If(Count() OVER (LastPeriods(7,[Axis.Rows]))=7,[Value],null) AS [mavg2] Can anyone please help how to solve this challenge Regards, Zoheb
Link to comment
Share on other sites

  • 1 year later...

Expression like below gives you difference between two moving averages:

Concatenate(Sum([col1]),"$",sum([col2]))

THEN Avg(Integer(Split([Value],"$",1))) OVER (LastPeriods(7,[Axis.Rows])) -

Avg(Integer(Split([Value],"$",2))) OVER (LastPeriods(7,[Axis.Rows]))

THEN If(Count() OVER (LastPeriods(7,[Axis.Rows]))=7,[Value],null) as [difference]Concatenate both columns by some separator like "$" then use each column value in respective moving average expression.

Similarly, you can apply your percentage formula as well before last THEN post-aggregation clause.

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