Weighted Average by variable number of weeks

ToddKavanaugh
ToddKavanaugh
in Modeling

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

Tagged:

Best Answer

  • rob_marshall
    rob_marshall
    Answer ✓

    @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

Answers

Categories