Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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....
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....
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,
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.
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;