curYTD flag

[#1]

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;

[swuehl · Accepted solution]

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.

[maxgro]

I tried your script and year to date flag seems correct

Do you have order date this year?

[Anonymous]

Hi,

your script work fine.

Have you control the date format (is only an idea).

[#5]

Nope

In my script the current year is 2011

[#6]

I dont have records for 2015 year, so my script is good or do i need to change more

[#7]

Yes I used Date function to remove time

[#8]

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

[Anonymous]

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.

[Anonymous]

If the current Year is 2011 you should change the vToday variable and set this to a date of year 2011.

Regards.