Question on Year(Date)

[Anonymous]

Hi,

I'm trying to use the calendar Master script to create the Year dimension for my data set.

However, i am not sure why some of the year appears as '-' (see Row 7).

Am i doing something wrong? Or do need to first format my date field upon loading?

Row	Year	sum(Value)
Row 1	2009	279.49
Row 2	2010	0.00
Row 3	2011	191,826.79
Row 4	2012	8,593.27
Row 5	2013	101,875.40
Row 6	2014	2,015,958.22
Row 7	-	5,142,751.10

SDate is the date field I am using.

CalendarMaster:

LOAD

    Date(SDate) AS SDate,

    Year(SDate) AS Year,

    'Q' & Ceil(Month(SDate) / 3) AS Quarter,   

    Month(SDate) As Month,

    Day(SDate) As Day,

    Week(SDate) As Week;

Load Date(MinDate + IterNo() -1 ) AS SDate While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

    Min(SDate) AS MinDate,

    Max(SDate) AS MaxDate

RESIDENT FactSales;

thanks

Derrell

[Anonymous]

thanks Daniel.

[jonathandienst]

Hi

I think your master calendar script is OK - I think the problem is that some of your transaction dates have a time component, while the master calendar has only days (integer). This means that the transaction dates do not map into the master calendar and the '-' year value is 'missing'.

Make sure that the transaction dates have no time component - in the load statement that loads the value field, make sure the date is defined something like:

     Date(Floor(SDate)) As SDate,

You don't need the Floor in the calendar because you are working with integers already.

HTH

Jonathan

[danieloberbilli]

Actually, I agree with the posting of Jonathan Dienst   - you should definitely try this out. I just saw that I was wrong regarding interpretation of the date fields (which would be actually done with date#().

Kind Regards from Flinders Street...good to see some people from Melbourne here

[Anonymous]

Thanks, Jonathan. Works like a charm!