Weighted Average by variable number of weeks




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)





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

Best Answer

  • rob_marshall



    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.



    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.




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


    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]



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




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




    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:











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