How to Group Weekly revenue into a customized Month (List)?

https://community.anaplan.com/t5/Content-Sandbox/REVIEW-REQUESTED-Convert-Values-from-a-445-Calendar-to-a-Normal/ta-p/59839

Wow! What a great question - as it turns out, I just submitted an article that converts a weekly calendar to a Gregorian Calendar!!

You had posted a question several weeks ago about this so I went ahead and detailed out the steps. So thank you! I also gave you credit in this artcle.

Here's the link. It may seem a little lengthy but it's really not that bad - it took a lot longer to write this than it did to solve it.

If the link doesn't work let me know. I'll add it to this post.

Hi @JaredDolich,

Your model was a life saver, it just had one little problem I am trying to solve, your Time Setting module is only able to read the 445 week format from Anaplan itself but not Broadcast calendar. Using August 2020 for example, for Gregorian Calendar, it includes the last 2 days of week 31, 32, 33, 34, 35 and first day of 36; in 445 calendar, it contains week 31, 32, 33 and 34; and in broadcast calendar, it contains Week 31, 32, 33, 34 and 35 (Aug 24 - 30).

Broadcast Calendar:

445 Anaplan Time

In this scenario, when the module calculates the Day from End, it's calculating based off revenue from week 34 and not week 35. so the revenue won't be accurate.

Ah, okay. I used a 445 calendar, but you can use the calendar that fits your broadcast calendar.

Since August is a 5 week month in your broadcast calendar you'll need to adjust the 445,454, or 544 combination to match that calendar, as well as setting the correct fiscal month begin. I chose February but yours may be January judging by your screenshot. I believe yours would be a 454. And don't forget to configure for the 53rd week.

@jimfeng5 let me know if I've over simplified the challenge. I'm very confident we can get this to work.

@JaredDolich, I cannot express enough on how much I appreciate your help on this! As a new Anaplan modeler, building this model is truly a challenge for me. Thank you so much for guiding me through this.

I believe we may have oversimplified this. If you look at the attached calendar below, the broadcast calendar is actually a combination of 445, 454 and 544. For this reason, I had to create a module in my model to group Anaplan weeks into correct month to capture all the revenue in broadcast month, and I created another line called Other Revenue to store the stub period revenue calculated using "Days from Start" and "Days from End".

Using 2020 and 2021 as an example, below I'm laying out the # of weeks included in each broadcast month.

Jan 4

Feb 4

Mar 5

April 4

May 5

June 4

July 4

Aug 5

Sep 4

Oct 4

Nov 5

Dec 4

2021:

Jan 5

Feb 4

Mar 4

April 4

May 5

June 4

July 4

Aug 5

Sep 4

Oct 5

Nov 4

Dec 4

Broadcast Calendar:

andre.lie

I think we need to have a month to week mapping as below

Following on what @JaredDolich has detailed in his article

Now assuming we have this weekly sales, where Wk 31-34 is 700 each and Wk 35 sales is 900

The unadjusted monthly sales is 3700, less 5 days for Wk 31 adjustment, plus 1 day for Wk 35 adjustment

Regards

Andre

@andre.lie @JaredDolich

I have created a few more lines too assign each week under months. But this would require the end user to input the start week # and end week #.

andre.lie

If you prefer having 445 Month Start and End as input, I do not think you need to enter First Week and Last Week. Second, Third, Fourth Week number line items are not necessary either.

You can use:

- PERIOD function for the First Week Period : PERIOD('445 Month Start'),

- First Week Period + 1 / 2 / 3 for Second to Fourth Week Period,

- this logic to check if it is a 5-week month for Last Week Period : IF (Period End - Period Start + 1) / 7 = 5 THEN '1st Week' + 4 ELSE BLANK

Let's keep this conversation going!

I think we're really close and I think I finally understand the issue you have.

I really like @andre.lie idea to make your line items formulas rather than being dependent on someone entering them.

Is there any systematic way we can calculate how many weeks a month will have in the broadcast calendar, like if Sunday is within 3 days of the end of the month or number of holidays a month has, maybe there's a special calendar people in the broadcasting industry use?

If there's no systematic way, it means someone is making that decision and we need to capture that decision making in a system module that contains week count by month. From there we can calculate everything else.

We're so so close. Let's get some closure on this one!

@JaredDolich, @andre.lie, after our conversation, I looked into the creation of broadcast calendar and there is a logic that "The key link between the broadcast and Gregorian calendars is that the first week of every broadcast month always contains the Gregorian calendar first day of the month. For example, if January 1 falls on a Saturday, then the broadcast calendar year would begin on the preceding Monday, December 27. Broadcast January would then have five weeks, ending on January 30, and the four weeks of broadcast February would begin on January 31. The number of weeks in a broadcast month is based on the number of Sundays that fall in that month with the period ending on the last Sunday of the month."

Okay! Now we're on to something.

Do you need to see that worked out in Anaplan, or do you think you've got it?

So, so cool! I just learned something new - Is that rule typical in the broadcasting industry?