Adust daily figures to preserve monthly totals while switching from a weekly to a regular calendar

Hello community,

I am trying to adjust my daily sales forecast so that the monthly calendar total matches the monthly "weekly-calendar" total, while keeping my seasonality as close to reality as possible.

A simple picture will tell more than 100 words.

2020-05-14 23_03_10 - Base.png

.Spreadsheet here: https://docs.google.com/spreadsheets/d/1rL4AnoCCTUAX9zXmF0tW6wRhvYhddUdm_buRgf_5G9s/edit?usp=sharing

I am trying to get the green column 'resultNetSales'.

In order to do that, I need the orange column 'sum of daily sales over calendar month' to compute the sum of all netSalesEuroByDay that have the same calendarMonth as the current day.

In Excel, I would simply use the " =SUMIF($F$6:$F,F8,$D$6:$D) " formula.

I would have expected the formula " netSalesEuroByDay[SUM: calendarMonth ] " to give me that result, but it keeps throwing a horrible error:

2020-05-14 23_10_18 - Base.png

Any idea on what formula to put in my orange column?

Or another way of achieving the result column differently?

Thanks a lot,

Accepted answers

fxlepoutre

Hello @JaredDolich ,

I finally found another technique that solved my issue, using the "cumulate" formula, and resetting it at every month change.

It looks slightly dirty, but it happens to work fine and achieve the desired result of using my daily figures in the most accurate way possible.

So here are the line items that I ended with:

In a system module at the "Day" level:

2020-05-19 15_15_48 - Base.png

- mid-month date => using the 15th of the month assures me to always find the proper Gregorian calendar month for that day (should work well whatever your time settings).

- start => getting the first calendar day of the Gregorian calendar month

- end => getting the last calendar day of the Gregorian calendar month

- calendarMonth => Getting the Gregorian calendar month of the date, as a Time Period.

In a calculation module, at a Day level also:

2020-05-19 15_22_06 - Base.png

- netSalesByDay => the input, which are the sales quantities, for every day of the 445 calendar.

- monthlySalesOver445Calendar => the monthly total of the 445 calendar sales, for the assigned Gregorian calendar month of each day

- cumulatedSalesOverGregorianCalendar => cumulates the daily sales, but resets every time we change calendar month.

- monthlySalesOverGregorianCalendar => returns the cumulated sum for the last day of the assigned Gregorian calendar month of each day, which corresponds to the total monthly sales of the Gregorian calendar month. (the orange column of the initial screenshot)

- adjustedNetSalesByDay => returns the adjusted daily figure, proportionally distributed according to the monthly sales (the green column in the initial screenshot).

Hope it can help someone else.

Cheers,

All comments

JaredDolich

@fxlepoutre

Great question - one that is asked a lot.

Next week, this article is scheduled to be published which converts a 445 calendar to a Gregorian.

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

The short story is that you need to create a system module at the daily level using the weekly calendar. From there it's a matter of mapping - which some call a calculation module. See if the article above helps. If not, I can give you more specifics.

fxlepoutre

Hello Jared,

Thank you for your quick answer and for sharing this article in advance.

I assume that I need to run this "adjusted sales" calculation in a Month time scaled line item, and then use that figure in my orange column as a MONTHVALUE("adjusted sales") ?

Correct me if I am wrong, but your method seems to be going from a month level to another month level, and doesn't seem to take into account the daily values. For instance, if the month ends a Friday, and there are no sales on Saturday and Sunday, there should be nothing moved to the next month. How do you handle that?

Thanks,

Misbah

@fxlepoutre

If you implement @JaredDolich 's solution I think you should be able to get the expected output. You may have to tweak it as per your requirement considering you are looking at daily timescale.You can change the dimensionality of the module and for few line items where you are pulling the daily Sales

Misbah

@JaredDolich

One query on Planning Month to Gregorian Month Conversion article - Is there a specific reason that we are keeping month values to be an input field in the system module. There can be cases where planning spoke model will just have one year of a time scale and imagine what happens when there is yearly rollover process.This field has to be maintained every year.

Now we can argue that we could add Time Range to the system module for multiple years and set it up for all years but that still comes under maintenance. Here is what I was thinking - If we had created one more list to maintain Fake months along with the Codes system could have easily populated the month value irrespective of the year hence removing the maintenance.

Great article and it is surely going to help people who want to see numbers in planning month vs book month

JaredDolich

@fxlepoutre

Good query. The conversion from 445 to Gregorian happens at the daily level, so you should be good there.

The trick is to determine the right allocation. In my example I divide by 7 and assume each day is equal which we know that is unrealistic. Ideally, you will have a % to total by day of week to better allocate the proportions.

The other nuance is what to do with the first and last month. Ideally, the entire year is preserved. Meaning if the 445 calendar year adds up to $1 million then the Gregorian Calendar year adds up to $1 million. Two issues with that:

445 calendar is 52 x 7 = 364 days; whereas, Gregorian is 365 days (or, 366 depending on if there's a leap year).
445 every 5 to 6 years will add an additional week, or 53rd week to make up for the missing day each year.

So it comes down to the level of complexity you're willing to resolve. A perfect solution will require a bit more work; whereas, a "close enough" solution can be managed rather quickly with some simple mapping as described in the article.

You're on the right track. I happen to agree that the daily allocation is necessary. I suppose you can further simplify it by manually entering the monthly allocations and bypass days altogether. That would guarantee that your calendars at the annual level would reconcile since you'll need to make sure what you take away from a month is given back to another.

Hope that helps.

JaredDolich

@Misbah

I love how you think!

A faked out month would work but I suppose the trade off is that you can't use any of the built in time functions.

But to your point, using the calendar or time range, of course, means that we've added an administrative task for someone. But, some good news, is that there's no hard-coding so the only admin task would be to move the calendar forward or extend it.

Pros and Cons. You're absolutely right though -

Misbah

@JaredDolich

I don't think I articulated things pretty well. Apologies! I was not talking about changing the entire dimensionality of the module from Native to Fake time scale Here is what I was talking about.

It is mentioned that this is ok to keep "Month Number" Line item a manual field. I have listed out my reason why it might not be ok when there is a solution available with you

Solution to this:

Step 1: Create a list, Insert numbers from 1-12 and mention your months in the code of the list. It can be as per requirement but I am just keeping 1 as Jan and 12 as Dec

Step 2: Create one line items in the SYS Module and call it Months without Year

Months without Year = LEFT(NAME(ITEM(TIME)),3)

Step 3: In your existing Month Number Line Item write the formula

Month number = FINDITEM(TIME, Months without Year)

This will ensure that we don't have to maintain this line item every year all the time.

fxlepoutre

Hello @JaredDolich ,

I finally found another technique that solved my issue, using the "cumulate" formula, and resetting it at every month change.

It looks slightly dirty, but it happens to work fine and achieve the desired result of using my daily figures in the most accurate way possible.

So here are the line items that I ended with:

In a system module at the "Day" level:

2020-05-19 15_15_48 - Base.png

- mid-month date => using the 15th of the month assures me to always find the proper Gregorian calendar month for that day (should work well whatever your time settings).

- start => getting the first calendar day of the Gregorian calendar month

- end => getting the last calendar day of the Gregorian calendar month

- calendarMonth => Getting the Gregorian calendar month of the date, as a Time Period.

In a calculation module, at a Day level also:

2020-05-19 15_22_06 - Base.png

- netSalesByDay => the input, which are the sales quantities, for every day of the 445 calendar.

- monthlySalesOver445Calendar => the monthly total of the 445 calendar sales, for the assigned Gregorian calendar month of each day

- cumulatedSalesOverGregorianCalendar => cumulates the daily sales, but resets every time we change calendar month.

- adjustedNetSalesByDay => returns the adjusted daily figure, proportionally distributed according to the monthly sales (the green column in the initial screenshot).

Hope it can help someone else.

Cheers,

Quick Links

Can you help?

Unanswered questions

Where can I find export file after running export process using machine task in a workflow?
I am testing export actions using workflow but its not generating any file when I am running the workflow, but the same process is generating export file when ran from the Actions→Process section. Is their any issue with the workflow functionality? Also, If I want to generate a file after completion of workflow and pull…
Automating Flat File Uploads to Anaplan Data Orchestrator (ADO) – Best Practices and Methods
Hello Anaplan Community, I am seeking guidance on the most effective ways to automatically upload flat files to Anaplan Data Orchestrator (ADO). Currently, our process involves loading flat files to an Anaplan Datahub using Anaplan APIs and custom Python scripting. With Anaplan Data Orchestrator's capabilities for data…
OAUTH 2.0 vs Google Sheets Extension
Hi, My org is looking to explore leveraging Google Apps script to extract data out of Anaplan and update already built templates in Google Sheets. We use the Anaplan Extension currently but find it crashes often with the amount of tabs, data, etc. Has anyone used both Anaplan OAUTH 2.0 API and Goolge Apps Script to write…