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
Answers
-
Hello, I'm trying to leverage your post to sum Weeks into 13 4-Week Periods.
Background: Client would like a 13 4-Week Calendar beginning week of 1/6/2019 to 1/12/2019, which I already confirmed Anaplan's native 13 4-Week Calendar options (Day nearest end of Month) and (Last Day in Month) does NOT allow.
Attached are a couple of screenshots of the direction I was going after reading your post, but did not have success.
Would you be able to go into more detail for bullet "c" under Option 1? I got a bit lost at this section.
Also, would you be able to clarify bullet "1" under Option 2 how you would map Week 1, Week 2, Week 3, Week 4 to Month?
Replacing my Periods with normal months (for sake of example) based on your instructions I would do the following:
List Member/Property
Jan/Week 1
Feb/Week 2
Mar/Week 3
Apr/Week 4
However, I would need the following to occur:
Jan: Week 1, Week 2, Week 3, Week 4
Feb: Week 1, Week 2, Week 3, Week 4
Mar: Week 1, Week 2, Week 3, Week 4
Any screenshots you can provide for you solution would be much appreciated!
Thank you!
0 -
Hi Joshua,
I think you're nearly there actually! The key thing to realise is that SUM is not going to work on General Weeks, as there is no recognised hierarchy. It just returns zeroes (as you've demonstrated). However, if your formula adds Week 1 + Week 2 + Week 3 + Week 4 for each month then it will work. You might need to flip your mapping table round so you have the Week 1 to Week 4 list and L2 Months as dimensions, with a line item for the General Week that is formatted on the timescale, and relates to the appropriate intersection (eg the data point for Week 1 of Jul-19 is 'W/c 1 Jul 19' or whatever the start date of your first week is).
Just remember you need to copy the formula 4 times, changing the week number (1-4) and leaving everything else the same, and add them all together, to give you the summation. As I mentioned in my initial post, there's probably a better way to do it, but I know it will work this way
At least you've only got a maximum of 4 weeks in each of your months, look on the bright side 🙂
Let me know how you go
Steve
0
