How to calculate the moving average based on condition?
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Labels:
- Preparation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you!
