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;
Use varMaxDate as fourth argument in your Year2Date function call.
Sure Will try that
I tried it is working thanks a lot
What about for month?
how to change curmtd flag
Like can you tell me how to assign in vtoday
Year2Date(TempDate, 0, 1, $(varMaxDate))*-1 AS CurYTD_Flag,
Year2Date(TempDate,-1, 1, $(varMaxDate))*-1 AS LastYTD_Flag,
InMonthToDate(TempDate, 0, 1, $())*-1 AS CurMTD_Flag,
InMonthToDate(TempDate,-1, 1, $())*-1 AS LastMTD_Flag
This is my script which variable should I assign to calculate CurMTD_Flag
Year2Date(TempDate, 0, 1, $(varMaxDate))*-1 AS CurYTD_Flag,
Year2Date(TempDate,-1, 1, $(varMaxDate))*-1 AS LastYTD_Flag,
InMonthToDate(TempDate, 0, 1, $())*-1 AS CurMTD_Flag,
InMonthToDate(TempDate,-1, 1, $())*-1 AS LastMTD_Flag
This is my script which variable should I assign to calculate CurMTD_Flag
Hi,
try this:
InMonthToDate(TempDate, $(varMaxDate), 0)*-1 AS CurMTD_Flag,
InMonthToDate(TempDate, $(varMaxDate), -12)*-1 AS LastMTD_Flag
Regards.
Its working thanks a lot
If you dont mind,
I do have one more question
Can I ask
Hi,
Yes, sure, ask what do you need! 🙂