Time Based Dis-aggregation in 6 weeks

[lalit_behere]

We are facing an issue with Time Based Dis-aggregation.

Here is little summery about configuration and Problem.

We follow calendar of 4-4-5 ( Weeks per month Pattern), i.e

4 Weeks Month:- Jan, Feb, April, May, July, Aug,Oct, Nov.

5 Week Month:- March, June, Sept, Dec

So we consider 52 Weeks in an year.

There are 365 Days = 52 Weeks + 1 Day. Now, after 6 years we are getting 1 Week extra in the Month of  DEC 2015 ( 6 Weeks )

We have a macro which calculates the value of Time Based Disag. (TBD) Key Fig. on the basis of Previous years Demand History Overrides.

Macro Step, which specifically works for December month
If Values of previous 2 years are available                              (It checks relative to current week) Then Time Based Disag. for current week (Week x/2015) = {              [    Demand History Override (Week x/2014) +     Demand History Override (Week x/2013)      ] / 2 } Else Time Based Disag. for current week (Week x/2015) = Demand History Override (Week x/2014) End If

Issue:

• Previous years (2013 & 2014) has 5 weeks each in Dec.
• 6^th Week of Dec 2015 getting values calculated from 1^st weeks of Jan 2014 and Jan 2015.

I've attached an image file describing the issue.  Kindly check and let me know if there is a way to resolve it without any changes to macro configuration. If macro need to be change then what are the additional steps I need to add ?

TIA..

Regards

Lalit

[Former Member · Accepted solution]

Hi Lalit,

No matter what you do you will always run into the 53 week versus 52 week problem.  You may be reading data from a 52 week year to a 53 week year or from a 53 week year into a 52 week year.  I therefore think you need a more complex solution!

Suggest a new planning book with several planning book key figures in it.  You may consider a new planning area for this.

What you have to do is determine that (for example) 2014 is the 1st historical year for 2015 and that has 52 weeks and the second historical year for 2016 which has 53 weeks.   Next write a macro to take the Demand History override from 2014 and move it forward 52 weeks into the auxiliary key figure for 2015 and 52+53 weeks for 2016 into the 2016 auxiliary key figure using timeshift macro function.  to complete 2015 you need a time-shift macro to write forward the 2013 data also.

When writing the data from a 52 week year into 53 week year you need a further step in your macro to add week 52 to week 53 and when writing a 53 week year to a 52 week year you ignore the 53rd week.

This gives you the numbers you need in the weeks you need and sorts out the 53 week issue.  You now calculate the average into a database key figure so you can read it in any planning book and you are ready to.

What this means is you need to write a macro to roll forward each relevant year into an auxiliary key figure for that year.  Now group the macros together so you roll forward year 1 then roll forward year 2 and finally work out the average and save it in the 3rd key figure.  This means all the calculations for the next year are done and ready to use in your main planning book.

Downside of this is you need to write a set of macros for every year to take account of the 53 week issues that arise but hopefully this will not be too much of a problem.

Regards

Laurence

[lalit_behere · Accepted solution]

Hi Laurence,

That's a very good suggestion.

I'll try to develop it and present it to business.

seems you have already faced similar issue.

For the work around I have proposed another solution till the end of 53rd week.

As the values are getting shifted 1 week back, I simply created a macro to shift them a week ahead. (Till 53rd week). But this is temporary. After 53rd Week we again need to check the scenario. Till that time I'll try to develop the solution you suggested.

Thanks Again.

Regards

Lalit

[Former Member]

Wow.

What is the planning bucket profile used here ? I presume it is all in weeks and the macro is also executed in weeks view.

Can you afford to execute the macro in Months and then check the result in weeks view? The final result will depend on your storage buckets profile of the planning area though. I also assume your storage buckets is based on weeks and the stated fiscal periods ?!

Check the storage period view to be reassured of how the dis aggregation is happening and then make according decisions.. viz. to write another macro using some function like WEEK() and YEAR () e.g. when Week = 53, do something different

[Former Member]

HI Lalit

I think you need a week 53 logic, some thing as follows:

If the last week of December , the donot apply your logic above but see if you can just copy the prior week's TBD?

If the week is partial, then use the proportion of prior week's TBD, example, you have 3 days in week, then only get 3/7 of the previous week.

But this is subject to validation of how good it fits your business user needs. It is some what like an approximation.

[lalit_behere]

Hello Sir,

Thanks for the response. I was also thinking in the same direction.

Is there any in-built function in macros where I can get the Week No. of the Fiscal Period ?

I searched for Date functions of Macros, but didn't get any which deals with Weeks.

Regards

[Former Member]

Did you try the WEEK() function?

WEEK()

WEEK( X ) returns the week of a date X that is expressed in integer format with reference to the date 0001/01/01. For example, WEEK( DATE ( 20001220 ) returns 51. The argument can be either a number that you enter in an Operator/function dialog box, or one of the macro elements cell, row or column from the planning table or the auxiliary table.