master calendar with same formats as in sever

[Anonymous]

rwunderlichswuehlmrkachhiaimpgwassenaar i am trying to build a master calendar but failing as the time stamp format  in the sever  and the calendar which i am trying to build is in different formats , i request you to help me to build a master calender so that i can extract day ,time , hour , week , month,month name and all possible fields from it

[ajsjoshua]

Hi,

Try this in your script

/********************mastercalendar**********************/

MinMax:

LOAD

  MIN(Yourdatefield) AS MinDate,

  MAX(Yourdatefield) AS MaxDate

RESIDENT tablename;

LET vMinDate = NUM(PEEK('MinDate',0,'MinMax'));

LET vMaxDate = NUM(PEEK('MaxDate',0,'MinMax'));

LET vToday = $(vMaxDate);

//*****Temp Calendar*****

TempCal:

LOAD

  DATE($(vMinDate) + ROWNO() -1) AS TempDate

AUTOGENERATE

  $(vMaxDate) - $(vMinDate) + 1;

DROP TABLE MinMax;

//*****Master Calendar*****

MasterCalendar:

LOAD

TempDate AS Yourdatefield,

WEEK(TempDate) AS Week,

YEAR(TempDate) AS Year,

MONTH(TempDate) AS Month,

DAY(TempDate) AS Day,

WEEKDAY(TempDate) AS WeekDay,

AutoNumber(YEAR(TempDate) & MONTH(TempDate), 'MonthID') as [MonthID],

'Q' & If(CEIL(MONTH(TempDate))>=8 AND MONTH(TempDate)<=10 ,1,If(CEIL(MONTH(TempDate))>=2 AND MONTH(TempDate)<=4 ,3,If(CEIL(MONTH(TempDate))>=5 AND MONTH(TempDate)<=7 ,4,2)))  AS Quarter,

date(yearstart(TempDate,1,8),'YYYY') &' - '& 'Q' & If(CEIL(MONTH(TempDate))>=8 AND MONTH(TempDate)<=10 ,1,If(CEIL(MONTH(TempDate))>=2 AND MONTH(TempDate)<=4 ,3,If(CEIL(MONTH(TempDate))>=5 AND MONTH(TempDate)<=7 ,4,2))) as YearQuarter,

AutoNumber(date(yearstart(TempDate,1,8),'YYYY') &' - '& 'Q' & If(CEIL(MONTH(TempDate))>=8 AND MONTH(TempDate)<=10 ,1,If(CEIL(MONTH(TempDate))>=2 AND MONTH(TempDate)<=4 ,3,If(CEIL(MONTH(TempDate))>=5 AND MONTH(TempDate)<=7 ,4,2))),'QuarterID') as QuarterID,

DATE(MONTHSTART(TempDate), 'MMM-YYYY') AS  MonthYear,

WEEK(TempDate) & '-' & YEAR(TempDate) AS WeekYear,

date(yearstart(TempDate,1,8),'YYYY') AS YearFiscal,

//INYEARTODATE(TempDate,$(vToday),0,8) * -1 AS CurYTDFlag,

//INYEARTODATE(TempDate,$(vToday),-1,1) * -1 AS LastYTDFlag

INYEARTODATE(TempDate,$(vToday),0,8) * -1 AS CurYTDFlag,

INYEARTODATE(TempDate,$(vToday),-1,8) * -1 AS LastYTDFlag

//INYEARTODATE(TempDate,TempDate,0,8) * -1 AS CurYTDFlag,

//INYEARTODATE(TempDate,TempDate,-1,8) * -1 AS LastYTDFlag

RESIDENT TempCal

ORDER BY TempDate ASC;

DROP TABLE TempCal;

[Anonymous]

please do look at the attached file , the fields i am derviving is not getting associations with the the other fields

[settu_periasamy]

Hi,

There is problem with you Date format. You can change format in the Environment variable, or you need to do the change in date / timestamp function..

try to change like this..

[Anonymous]

I TRIED IT STILL NOT WORKING ,CAN YOU PLEASE SEND THE APP

[settu_periasamy]

Can you post the excel sample?

[Anonymous]

please find the sample

[settu_periasamy]

Hi,

May be check the attachment..

Calendar with time (timestamp)