How to calculate the moving average based on condition?

Question

Hi Team,

I would like to find two solution for the same requirement.  I tried Multi-Row formula but not getting the expected result its adding the values without condition. It will be great if any one can assist me.

* Enclosed my sample data. In that, future date values need to be calculate based on condition (service and city).Example: future data is starts from Row no 38 (highlighted in orange) right now the values is 0. Need to find the average based on previous value . Screen short 1 : Row no 38 : sum_A expected value should be 351 (excel formula =(SUM(D36,D31,D24))/3). Need to calculate 3 previous same service and city value and divide by 3 . Same way need to find the values future date values for all columns 
Expected Result:  Same way have to find the remaining all future date values.

2.Need to find the subtotal based on service. Expected Result

Thanks

Alteryx Raw Data.xlsx

Gmurugan · Answer

Thank you!

Gmurugan · Answer

Hi @Jonathan-Sherman

Future date values need to pick dynamically in previous row calculated value.

Below is the result after Multi Row formula. The logic should also need be consider the future predicted value.

Any suggestion?

Thanks

Geetha

geraldo · Answer

@Gmurugan

The formula was thus verifying whether the 3 previous periods. I believe you have to adjust the average division. I did proportional, I didn't divide all by 3. if you have 3 items by 3 if you have 2 items by 2 . and by 1 Make the necessary adjustments

if IsNull([Row-1:City]) then [Sum_A]
elseif [Sum_A] == 0 and [City] == [Row-3:City] then Round(([Row-1:Sum_A]+[Row-2:Sum_A]+[Row-3:Sum_A])/3,1)
elseif [Sum_A] == 0 and [City] == [Row-2:City] then Round(([Row-1:Sum_A]+[Row-2:Sum_A])/2,1)
elseif [Sum_A] == 0 and [City] == [Row-1:City] then Round(([Row-1:Sum_A])/1,1)
else [Sum_A]
endif

attached a workflow example

ExampleAlteryx.yxmd