How to calculate the moving average based on condition?

[Gmurugan]

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.

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

[Jonathan-Sherman]

Hi @Gmurugan,

You could do this using the multi-row formula tool ensuring you group on the right fields, which in this case would be service and city. Transposing before the multi-row allows us to perform this calculation across all columns at once rather than one by one (we'll need to include Name in the multi-row grouping also if that's the case).

I've attached my workflow to help you work through, if you have any questions just let me know!

Kind regards,

Jonathan

[geraldo]

@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

[Gmurugan]

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

[Gmurugan]

Thank you!