Weighted Average by variable number of weeks

Occasional Contributor

Weighted Average by variable number of weeks

@rob_marshall

 

I am looking for some guidance on how to do weighted averages based on a selected amount of weeks? Our call center planning is in need of looking at weighted averages based on a variable amount of weeks

For example, a user selects the number of weeks they want to average the results by and based on that amount of weeks, there is different weighting by percentage..

Module: Made up of 2 lists(Weekly Percentages; Number of Weeks Average') and 1 line item (Percent)

 

ToddKavanaugh_0-1581607950234.png

ToddKavanaugh_1-1581607950240.png

 

I have set up a list for 'Number of weeks average' to select the # of weeks we want to average, and a list for 'weekly percentages' for the % needed for the corresponding week..  i.e. if you choose a 5 week average, week 1 will be weighted at 24%, week 2 at 22% and so on..If you choose 6 weeks, those percentages change according to what percentage is in the module

Can assist me with this? Have any examples or can you point me in the right direction? I am drawing a blank and I feel like it shouldn’t be that complicated...

3 REPLIES 3
Highlighted
Master Anaplanner/Community Boss

Re: Weighted Average by variable number of weeks

@ToddKavanaugh 

 

Great question, let's see what we can do.

 

First, you will need a SYS Module for the weighted averages, basically this is to tell the system which one is next in line.

 

2020-02-13_09-36-59.png

In your SYS Admin module, or you global module, you will need to have a setting for which weighted average to use first.  Think of this as a global setting and should just be one line item (no dimensionality) formatted as your weighted average list.

 

2020-02-13_09-37-18.png

 

Next, in your SYS Time filter module, you will need a couple of line items: Previous Week?, Weighted Percent, and Previous Weighted Percent.

Formulas:

Pervious Week formatted as a boolean: it could be curentperiod() -1, but I already had something else

Weighted Percent formatted as your Weighted list: IF Previous Week THEN 'SYS-ADMIN'.'Week 1 Weighted AVG' ELSE NEXT(Previous Weighted %)

Previous Weighted Percent formatted as your Weighted list: 'SYS-D Weighted %'.Previous Week %[LOOKUP: Weighted Percent]

2020-02-13_09-57-49.png

 

This is why using Time is so nice, because you can use the functions Previous() and Next().

2020-02-13_09-39-01.png

 

 

Now, you have the breadcrumbs based on the week, to know which weekly percentage to use.

 

2020-02-13_10-00-21.png

 

In your calculation module, create a line to get the percentage which has the following formula: 'VAR02 Weighting'.Percent[LOOKUP: 'SYS-C Time Filter by Week'.Weighted Percent, LOOKUP: 'VAR02 Variables'.Average # of Weeks]

 

Based on what is chosen as the "driver" the percentages will change:

2020-02-13_10-04-27.png

 

 

2020-02-13_09-55-10.png

 

2020-02-13_10-03-10.png

 

2020-02-13_10-07-02.png

 

 

Hope this helps and let me know if this works for you,

 

Rob

Highlighted
Occasional Contributor

Re: Weighted Average by variable number of weeks

That is great.  Itis functioning how I want. I can now select the amountof weeks and it will tell me the weighting of each week... Thank you so much... 

Highlighted
Master Anaplanner/Community Boss

Re: Weighted Average by variable number of weeks

@ToddKavanaugh ,

 

Glad it worked out!