Calendar Date shows as number in master calendar

[#1]

Hi,

I have used example of master calendar and created application to show datewise sales.

but my date is showing as number instead of DD/MM/YY format.

Following is the code I used to create calendar.

QuartersMap: 
MAPPING LOAD  
rowno() as Month, 
'Q' & Ceil (rowno()/3) as Quarter 
AUTOGENERATE (12); 
     
Temp: 
Load 
min(PROCS_DATE) as minDate, 
max(PROCS_DATE) as maxDate 
Resident SalesData; 
     
Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 
DROP Table Temp; 
     
TempCalendar: 
LOAD 
$(varMinDate) + Iterno()-1 As Num, 
Date($(varMinDate) + IterNo() - 1) as TempDate 
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 
     
MasterCalendar: 
Load 
  TempDate AS OrderDate, 
  week(TempDate) As Week, 
  Year(TempDate) As Year, 
  Month(TempDate) As Month, 
  Date(MonthStart(TempDate), 'MMM-YYYY') as [Month Year],
  Day(TempDate) As Day, 
  ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 
  Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 
  WeekDay(TempDate) as WeekDay 
Resident TempCalendar 
Order By TempDate ASC; 
Drop Table TempCalendar; 

[petter · Accepted solution]

When you do calculations on dates you often lose the date-formatting and the dates turn into it's basic underlying numeric serial date representation.

To be sure to keep the date-formating you should wrap your fields in the Date() function in your last load.

     Date( TempDate) AS OrderDate      // like you do later in the load anyway in the same load....

[lironbaram]

hi

usually it's mean the order date is loaded as a number in another table

the connection will work fine

you can try this

Date(TempDate) AS OrderDate,

[MK9885]

Maybe try with

date(date#(TempDate, 'YYYYMMDD'), 'MM/DD/YYYY') as OrderDate

Also check the date syntax Temp Table and Variables varMin and Max date.

[divianjani]

HI Nitin,
Please Try below Script.

Date(38370,'MMMM.DD.YYYY') AS NewFiledName Resident TableName;

mention your date field name instead of 38370  and alias it then use resident table-name;