Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Friends,
Can anyone please correct me for the curYTD flag calculation code I am getting Flag 0 for all the dates. Is that correct or do I need to change something.
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min (date(OrderDate)) as minDate,
max (date(OrderDate)) as maxDate
Resident Orders;
drop table Orders;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
LET vDateToday = Num(Today());
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
date(TempDate) AS OrderDate,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
num( Month(TempDate)) as MonthNo,
Month(TempDate)*100+Day(TempDate) as DayOfYear,
// Month(TempDate)&'-'& Year(TempDate) as MonthYear,
Date(MonthStart(TempDate),'MMM-YYYY') as MonthYear,
Year(TempDate) &Num(Month(TempDate)) as YearMonth,
//if(Year(TempDate)=WeekYear(TempDate), Week(TempDate)-Week(MonthStart(TempDate)) +1, Week(TempDate)+52 -Week(MonthStart(TempDate)) +1) as WeekinMonth,
//'Week' & Ceil(Day(TempDate)/7) as WeekInMonth,
Ceil(Day(TempDate)/7) as WeekInMonth,
Day(TempDate) As Day,
//InYearToDate(TempDate, $(vToday), 0) *-1 as CurYTDFlag,
//InYearToDate(TempDate, $(vToday), -1) *-1 as LastYTDFlag,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekYear(TempDate)& Num(week(TempDate)) as YearWeek,
WeekDay(TempDate) as WeekDay,
Num(WeekDay(TempDate)+1) as WeekDayNo,
Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CurYTD_Flag,
Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTD_Flag,
InMonthToDate(TempDate, 0, 1, $(vToday))*-1 AS CurMTD_Flag,
InMonthToDate(TempDate,-1, 1, $(vToday))*-1 AS LastMTD_Flag
Resident TempCalendar
Order By TempDate;
Drop Table TempCalendar;
store MasterCalendar into [$(vQVDpath)MasterCalendar.qvd] (qvd);
drop table MasterCalendar;
in my order date time is also displaying so i used the below script and changed the date format but when i am using that i am not getting all values.
When i used
Date(Floor(Date#(YourDate, 'MM/DD/YYYY hh:mm:ss TT')), 'MM/DD/YYYY') as YourDate
Only Customer Table data is visible...No data is loading from other tables..
All field values of your timestamp have the exact same format?
Like in employees table the hire year should be of same format?
No, a year field should have not the same format as a date field.
Please take your time to read and understand
Okay Thanks a lot for your help