Highlighted
Contributor

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

Hi Anaplanners,

For my organization, we have revenues recorded by broadcast period and we have to report it by Gregorian month. Since the default Anaplan 445 timeline is not the same as broadcast period. I have to create a module that allows me to create custom grouping of the Anaplan week into my custom month.

 

Capture.PNG

Then, I've created another module with sum function to group the revenue. However, I can only select one year instead of multiple years. one of the requirement is to be able to show forecasted revenue for the next 5 years. Does anyone have an idea how I can make that happen?

 

Capture1.PNG

thanks,

Jim

12 REPLIES 12
Highlighted
Master Anaplanner/Community Boss

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

@jimfeng5 

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.

https://community.anaplan.com/t5/Content-Sandbox/REVIEW-REQUESTED-Convert-Values-from-a-445-Calendar...

 

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

 

 

 


Jared Dolich
Highlighted
Contributor

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

Hi @Jared Dolich,

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:

Capture1.PNG 

 

445 Anaplan Time

Capture2.PNG

 

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.

Capture.PNG

 

Highlighted
Master Anaplanner/Community Boss

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

@jimfeng5 

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.

 

445Calendar.png


Jared Dolich
Highlighted
Contributor

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

@Jared Dolich, 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:

Capture.PNG

Highlighted
Certified Master Anaplanner

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

@jimfeng5 

 

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

Capture1.JPG

 

Following on what @Jared Dolich has detailed in his article

Capture2.JPG

Capture2b.JPG

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

Capture3.JPG

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

Capture4.JPG

Capture5.JPG

 

Regards

Andre

Highlighted
Contributor

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

@andre.lie @Jared Dolich 

 

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

 

Capture.PNGCapture1.PNG

 

Highlighted
Certified Master Anaplanner

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

@jimfeng5 

 

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

Highlighted
Master Anaplanner/Community Boss

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

@jimfeng5 

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!

 

 


Jared Dolich
Highlighted
Contributor

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

@Jared Dolich, @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."