Date Challenge

[#1]

Hi Folks,

Just looking for some advice on the best way to get round a date challenge I have.

I need to compare sales by day between 2014 and 2013, meaning Day 1 sales in 2013 vs Day 1 sales in 2014 etc using a line chart. The challenge is that Day 1 is not fixed, but just depends on when the season begins. Day one in 2013 was say 20 March 2013, and Day 1 in 2014 is 11 March. The counting of days excludes weekends and public holidays. What is the best way to allocate day numbers against dates that I have in my data i.e. dates for 2013 and dates for 2014, so that I can create the line graphs based on these day number allocations.

Thank you for all your help

Herbert

[Nicole-Smith]

Something like this (assuming you already have the day numbers in your data)...

Dimension 1: Day Number

Dimension 2: Year

Expression: sum(Sales)

[MK_QSL]

What Nicole has suggested is looking easiest way to achieve your goal.

If your requirements is something different, please provide sample data file...

[#4]

Thank you for the reply Nicole,

My challenge is not creating the chart, but in autogenerating day numbers from sale dates that are available, something I can add to a load script so that it generates the day number, but bearing in mind that these are not your ordinary day numbers that begin on the 1st of January, but say for example day one is 20.04.2013 in 2013.

Many Thanks

Herbert

[Carlos_Reyes]

The easiest way would be that you get or create a mapping table with the DayNumber that belongs to each Real Date. Then you can load that table and left join it to your calendar table.

The other way, not easy, is to code some sort of calculation condition (I'm thinking on IF's ) that defines the correct DayNumber for each Real Date while you create your calendar in the script.