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

[Mohd Zoheb Salim]

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

[Khushboo Rabadia]

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.