
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Create Dates from Year
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
Thanks.
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Have you looked at creating a master calendar script?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I assume MakeDate(2017) create only from Jan 01. Then you can use like
YearEnd(MakeDate(2017)) as MaxDate

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I just tried and when I select year, it is selecting only 01/01/YYYY for every year but not giving all months.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
// 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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yes, this is much simpler and smaller code compared to mine.
Thanks, it is generating all dates now.

- « Previous Replies
-
- 1
- 2
- Next Replies »