Week 53 in 2009 causing lag by one week

[Former Member]

Hi,

We are using keyfigures Sales History and Sales History (Last Year) in our data view and a default macro as below to calculate the Sales History (Last Year).

Step: Sales History Last Year : ( 182 Iterations : W 37.2007 ; W 09.2011 )

Row: Sales History Last Year ( Frm W 37.2008 ) =

Row: Demand History ( Frm W 37.2007 )

The problem we are facing is that there are 53 weeks in 2009. This causes one week lag in the data between keyfigures meaning,

'Sales History' W.1.2009 is shown up under 'Sales History Last Year' W.53.2009

'Sales History' W.2.2009 is shown up under 'Sales History Last Year' W.1.2010

Please let me know a suitable macro logic to correct this lag.

Regards,

Senthilrajan

[Former Member]

Hi Senthilrajan

I can't think of a easy solution so far...

I suggest the following, that might give you ideas...

1. Calculate how many weeks you have for each year.

You should be able to do so by getting the week number of the last day of each year.

You should ber able to do so with the following function: YEAR_EDATE() WEEK()

Store the result in a auxilary K figure. (easier is to do it for each bucket)

2. In a new step, use the result of first step with a if loop to decide if you should add 52 or 53 week.

It is a bit complex but should work...

Let us know how you get on!

Thanks and Regards

Julien

[Former Member]

Hi,

Thanks for the guidance. I am using function BUCKET_BDATE() to calculate the start date of the week and using an IF condition to compare the current week with the start date of W 53.2009 (I have hardcoded this).

For the weeks before W 53 2009 I am using the logic,

Row: Sales History Last Year ( Frm W 39.2008 ) =

Row: Demand History ( Frm W 39.2007 )

For the weeks after W 53 2009 I am using the logic,

Row: Sales History Last Year ( Frm W 40.2008 ) =

Row: Demand History ( Frm W 39.2007 )

Regards,

Senthilrajan