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;
Looks fine and is working for me.
Please check that varMaxDate is a Date in year 2015.
Otherwise there will be no master calendar OrderDate with curYTD flag equal 1.
Looks fine and is working for me.
Please check that varMaxDate is a Date in year 2015.
Otherwise there will be no master calendar OrderDate with curYTD flag equal 1.
I tried your script and year to date flag seems correct
Do you have order date this year?
Hi,
your script work fine.
Have you control the date format (is only an idea).
Nope
In my script the current year is 2011
I dont have records for 2015 year, so my script is good or do i need to change more
Yes I used Date function to remove time
Hi
In my script i have record from 2007 to 2011 so for 2011 it should show 1 but it is showing 0 can you please suggest me which values should I change
Ok.
But you use a variable vToday (is the current date, 2015, I think) so it is normal it dont find date of current year if you have a range from 2007 to 2011.
Maybe is better if you post sample data.
If the current Year is 2011 you should change the vToday variable and set this to a date of year 2011.
Regards.