TIMESUM with Generic Weeks, and other summation options

Highlighted
Certified Master Anaplanner

TIMESUM with Generic Weeks, and other summation options

Hi all,

 

I thought I'd just summarise some of my learning experiences with summing data using Generic Weeks, since I couldn't find an article that brought this all together.

 

First up, as you will probably know, you can't use the ordinary SUM function across the timescale if you've chosen Generic Weeks, as there are no built-in summation points.  Generic Weeks is perfect for an operational planning application, where you keep rolling from one week to the next, but of course it would be nice to sum this data into months and years for reporting purposes.  So what are the options?

 

Option 1: Weeks to Months using Calendar Days

Let's assume your requirement is to sum weeks to months using calendar days - ie if you have a w/c 26th Dec, then you want the 26th, 27th, 28th, 29th, 30th, 31st to sum to December, and the last day to sum to January.  So 6/7 of the week goes to December, and 1/7 to January.  

a. Build yourself two lists, L1 Years (to give yourself a year filter in your dashboards) and L2 Months, which you will use as a 'dummy' timescale. 

b. You then need to populate a start date as a property for each month (if this is a long list, use Excel to auto-generate the date based on the month and import it - it will understand the month description, even if Anaplan doesn't). 

c. Once you have the start dates, you can use a module to write the more complicated time-related formulae into line items, using DAY(), ADDMONTHS() and PERIOD() to give you all the reference points you need.  For example, the Start Week of the month is PERIOD(Start Date), and the End Week is PERIOD(ADDMONTHS(Start Date, 1)-1), or simply PERIOD(End Date) if you've already done that calc. 

d. Now you can write the ratios based on where the month-end falls in start and end weeks.  NB You may need to add 1 to the subtraction calcs to get the right 'inclusive' treatment of the start or end date itself.

e. Now, in your reporting module, which uses L2 Months, you can add line items for the Month (=ITEM('L2 Months')), Start Week ('L2 Months'.Start Week[LOOKUP: Month]) and End Week.  You're going to use TIMESUM for the weeks in between the start and end week, so add 1 to the Start Week and subtract 1 from End Week to give you Timesum Start Week and Timesum End Week respectively

f. Finally, your TIMESUM formula is TIMESUM({Module}.{Line Item}, Timesum Start Week, Timesum End Week) + {Module}.{Line Item}[LOOKUP: Start Week] * 'L2 Months'.Start Week Ratio[LOOKUP: Month] + {Module}.{Line Item}[LOOKUP: End Week] * 'L2 Months'.End Week Ratio[LOOKUP: Month]

 

A few things to note:

1.  if you have other summations you need to do as part of this reporting process (eg product, customer), you need to do them FIRST, in a separate module that still has the Generic Weeks as a list/timescale, using a standard SUM function.  TIMESUM requires all the other lists to be the same in source and target modules for it to work properly.  in other words, it's a two-step process

2. As you see from the TIMESUM formula above, I wrote my timescale-related line items back into the L2 Months list as properties.  You don't need to do this, just take them from the module where you've calculated them if you want.

3. You can get fancy on the ratios if you only want to include working days.  In my example above, you might want the whole of the w/c 26th Dec to go to December, as 1st Jan is both a Public Holiday AND a Sunday.  This can be quite fun to calculate and test whether its really doing what you want, but I suggest you get the basic fraction of 7 working first, and then build on it later

 

Option 2: Weeks to Months using 4-4-5

In some ways this is easier, as you don't need to calculate ratios of part-weeks.  It also doesn't need TIMESUM, which means that you can combine other list summations in your formula.  The downside is that it's much less easy to automate, as you need to manually assign each of the weeks to a month.  Perhaps someone can suggest a way to streamline it?

 

Anyway, here goes:

1. Again, you will need your L1 Years and L2 Months lists, but this time the properties/line items required are Week 1, Week 2, Week 3, Week 4, Week 5, all formatted on the timescale.

2. This is the manual bit - updating which week goes to which month.  Again, you might be able to do it in Excel, if there is some logic to it, but I'm guessing if there was you would have chosen the 4-4-5 weeks timescale option!  We have actually had a case where the mapping varied by customer, in which case it definitely needs an admin person to maintain all of the mappings

3. The reporting formula is really quite easy: {module}.{line item} [LOOKUP: 'L2 Months'.'Week 1'] + {module}.{line item} [LOOKUP: 'L2 Months'.'Week 2']  ....and so on until week 5.  It feels clunky and a bit manual, but it works fine.  Once again, because you're not using TIMESUM, you can add a SUM for products, customers etc

 

Ok, that's it I think.  I'm sure there are better ways to handle this, but I thought I would put something out there so we can start a conversation.

 

Cheers

 

Steve

Message 1 of 1