Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

curYTD flag

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;

24 Replies
swuehl
MVP
MVP

Use varMaxDate as fourth argument in your Year2Date function call.

Not applicable
Author

Sure Will try that

Not applicable
Author

I tried it is working thanks a lot

Not applicable
Author

What about for month?

how to change curmtd flag

Not applicable
Author

Like can you tell me how to assign in vtoday

Not applicable
Author

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

Not applicable
Author

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


Anonymous
Not applicable
Author

Hi,

try this:


InMonthToDate(TempDate, $(varMaxDate), 0)*-1 AS CurMTD_Flag,

InMonthToDate(TempDate, $(varMaxDate), -12)*-1 AS LastMTD_Flag

Regards.

Not applicable
Author

Its working thanks a lot

If you dont mind,

I do have one more question

Can I ask

Anonymous
Not applicable
Author

Hi,

Yes, sure, ask what do you need! 🙂