Create Dates from Year

[MK9885]

Hello,

I  would like to create dates out of Year.

I do not have Date field, no Month field but just Year Field.

I used MakeDate function and it just generate 4 dates for me

I've years from 2014 to 2017

and I want dates for each month for these years....

01/01/2014 till 12/31/2017

neelamsaroha1575‌

stalwar1‌

Thanks.

[sunny_talwar]

Have you looked at creating a master calendar script?

[Anil_Babu_Samineni]

Can you try this?

LOAD Date(MinDate+IterNo()-1) as Date 

While MinDate+IterNo()-1 <= MaxDate; 
LOAD MakeDate(2014) as MinDate, 
     MakeDate(2017) as MaxDate
AutoGenerate 1;

[MK9885]

I just tried and created dates out of it.

The Year field I have it as Dimension in one of the tables

And I created Master Calendar and just renamed the field in my Master to match the name of the Year field in my Dim.

This Dim is Static file and the values will not change....

So will that work?

Cus If I select Year from Master, the Years in my Dim is also selected accordingly as needed.

[Anil_Babu_Samineni]

I assume MakeDate(2017) create only from Jan 01. Then you can use like

YearEnd(MakeDate(2017)) as MaxDate

[MK9885]

I just tried and when I select year, it is selecting only 01/01/YYYY for every year but not giving all months.

[Anil_Babu_Samineni]

First run this code and show the images how you are getting for min and max dates

LOAD Date(MinDate+IterNo()-1) as Date

While MinDate+IterNo()-1 <= MaxDate; 
LOAD MakeDate(2014) as MinDate, 
     MakeDate(2017) as MaxDate
AutoGenerate 1

Or

LOAD Date(MinDate+IterNo()-1) as Date

While MinDate+IterNo()-1 <= MaxDate; 
LOAD MakeDate(2014) as MinDate, 
     YearEnd(MakeDate(2017)) as MaxDate
AutoGenerate 1

[MK9885]

// Date Dimension

// to load Quarters Full Name

QuarterNAME:

LOAD * Inline [

Quarter , QuarterFullName

Q1 ,FIRST

Q2 ,SECOND

Q3 ,THIRD

Q4 ,FOURTH

];

// to create Quarters ie Q1,Q2

QuartersMap: 

    MAPPING LOAD  

    rowno() as Month, 

    'Q' & Ceil (rowno()/3)  as Quarter 

    AUTOGENERATE (12); 

       varMinDate = num(date(mid('2013-01-01',1,10 ),'YYYY-MM-DD'));

//       varMaxDate = num(date(mid('2015-12-31',1,10 ),'YYYY-MM-DD'));

      //varMaxDate = num(date(mid('2017-12-01',1,10 ),'YYYY-MM-DD'));

       varMaxDate = num(date(today(),'YYYY-MM-DD'));

// Creating a Temporary Calendar

TempCalendar: 

    LOAD 

                   $(varMinDate) + Iterno()-1 as Num, 

                   Date($(varMinDate) + IterNo() - 1) as TempDate 

                   AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

//Date Dimension

MasterCalendar: 

Load 

trim(date(TempDate,'YYYYMMDD')) as [DateID],

date(TempDate,'MM/DD/YYYY') as [Effective Date],

    day(TempDate) as Day,

year(TempDate) as Years,

    ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

  Ceil(Month(TempDate)/3) as [Quarter Number],

    quarterName(TempDate) as [Quarter Name],

    yearname(TempDate) & 'Q' & Ceil(Month(TempDate)/3)   as [Quarter Year],

date(monthstart(TempDate),'MM') as [Month Number],

    num(month(TempDate)) as Num_Month,

    month(TempDate) as Month,                  

    date(monthstart(TempDate),'MMMM') as [Month Full Name],

    monthstart(TempDate) as [Calendar Month Start Date],

    monthend(TempDate) as [Calendar Month End Date],

date(monthstart(TempDate), 'MMM-YYYY') as [Month Year],

date(monthstart(TempDate), 'YYYYMM') as [Year Month Num],

week(TempDate) as Week,

    week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as [Week Year],

    week(weekstart(TempDate)) & '-' & Month(TempDate)   as [Week Month],

    weekDay(TempDate) as [Week Day]

Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar;

Drop Table  QuarterNAME;

I used this and it's generating dates and I linked my Master Calendar to My dimension with Year Key

[Anil_Babu_Samineni · Accepted solution]

You may mis understanding me, That's why you pasted entire script

Any how, That is what i meant to say if you want all years and links you can use some thing like below

tabCalendar: 

LOAD *, 

     Week(Date) as Week, 

     Month(Date) as Month,

     Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter,

     Year(Date) as Year   

LOAD Date(MinDate+IterNo()-1) as Date 

While MinDate+IterNo()-1 <= MaxDate; 

LOAD MakeDate(2014) as MinDate, 

     Now() as MaxDate

AutoGenerate 1;

This will return from Jan 01 2014 to 29 jun 2017. And it will split into month, year, week, quarter as well. I am sorry, if i mi read your question.

[MK9885]

Yes, this is much simpler and smaller code compared to mine.

Thanks, it is generating all dates now.