Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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;

Tags (1)
1 Solution

Accepted Solutions
swuehl
Not applicable

Re: curYTD flag

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.

24 Replies
swuehl
Not applicable

Re: curYTD flag

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
Not applicable

Re: curYTD flag

I tried your script and year to date flag seems correct

Do you have order date this year?

1.png

ggallina
Not applicable

Re: curYTD flag

Hi,

your script work fine.

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

Not applicable

Re: curYTD flag

Nope

In my script the current year is 2011

Not applicable

Re: curYTD flag

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

Not applicable

Re: curYTD flag

Yes I used Date function to remove time

Not applicable

Re: curYTD flag

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

ggallina
Not applicable

Re: curYTD flag

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.

ggallina
Not applicable

Re: curYTD flag

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

Regards.