4 Replies Latest reply on Oct 15, 2018 2:00 PM by Andrew M

# Moving Weighted Average

Hi all,

I'm trying to dynamically calculate a moving weighted average and was hoping for some help. I found a few similar posts but couldn't find a solution that would work with drilling down or rolling up.

I'm attaching a blank twbx and sample data. I'd like to show the moving average of Processing Time, weighted by Actions. I'd like the calculation to update based on the data displayed on the view. For example, if 4 quarters were displayed, the moving average would be based on the 4 quarters displayed. Or, if only the current month was displayed and drilled down to show individual days, the weighted moving average would be for the current month by day.

Here's an example of the desired outcome for a few days from the data set. I appreciate any help and guidance!

 Date Actions Processing Time Moving Weighted Average Processing Time*manually calculated* 1/1/2017 24 1 1 1/2/2017 55 2 1.70 1/3/2017 95 3 2.41 1/4/2017 39 8 3.43 1/5/2017 22 2 3.30
• ###### 1. Re: Moving Weighted Average

I used the calculation below

returns this

or this

compute using date

see the attached

Jim

1 of 1 people found this helpful
• ###### 2. Re: Moving Weighted Average

Thanks Jim, this is amazing! You always have simple good solutions.

I tried to implement in my real data set and I'm having an issue. It's my fault for trying to oversimplify the data set, I apologize.

I think the issue is that I have multiple records per date, so it's summing total Processing Time. See below, if I add two more records to the data set I get 19 moving avg wtd, but it should be 8.14, based on the actions weight.

• ###### 3. Re: Moving Weighted Average

Hi if I understand I think you can use this for the weighted avg process

dividing by the action count should get the avg and not  the sum (you may need to use just count)

Jim

• ###### 4. Re: Moving Weighted Average

Thanks Jim! I really appreciate the help.