topic Re: curYTD flag in QlikView

curYTD flag

Sun, 28 Jun 2015 17:09:47 GMT

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;

Re: curYTD flag

swuehl — Sun, 28 Jun 2015 17:37:35 GMT

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.

Re: curYTD flag

maxgro — Sun, 28 Jun 2015 17:41:48 GMT

I tried your script and year to date flag seems correct

Do you have order date this year?

Re: curYTD flag

Anonymous — Sun, 28 Jun 2015 18:07:26 GMT

Hi,

your script work fine.

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

Re: curYTD flag

Sun, 28 Jun 2015 18:24:12 GMT

Nope

In my script the current year is 2011

Re: curYTD flag

Sun, 28 Jun 2015 18:26:20 GMT

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

Re: curYTD flag

Sun, 28 Jun 2015 18:27:50 GMT

Yes I used Date function to remove time

Re: curYTD flag

Sun, 28 Jun 2015 18:30:43 GMT

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

Re: curYTD flag

Anonymous — Sun, 28 Jun 2015 18:32:23 GMT

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.

Re: curYTD flag

Anonymous — Sun, 28 Jun 2015 18:44:08 GMT

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

Regards.

Re: curYTD flag

swuehl — Sun, 28 Jun 2015 18:45:43 GMT

Use varMaxDate as fourth argument in your Year2Date function call.

Re: curYTD flag

Sun, 28 Jun 2015 19:09:14 GMT

Sure Will try that

Re: curYTD flag

Sun, 28 Jun 2015 19:13:41 GMT

I tried it is working thanks a lot

Re: curYTD flag

Sun, 28 Jun 2015 19:16:39 GMT

What about for month?

how to change curmtd flag

Re: curYTD flag

Sun, 28 Jun 2015 19:17:31 GMT

Like can you tell me how to assign in vtoday

Re: curYTD flag

Sun, 28 Jun 2015 19:21:43 GMT

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

Re: curYTD flag

Sun, 28 Jun 2015 19:22:47 GMT

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

Re: curYTD flag

Anonymous — Sun, 28 Jun 2015 19:41:37 GMT

Hi,

try this:

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

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

Regards.

Re: curYTD flag

Sun, 28 Jun 2015 19:48:57 GMT

Its working thanks a lot

If you dont mind,

I do have one more question

Can I ask

Re: curYTD flag

Anonymous — Sun, 28 Jun 2015 19:52:13 GMT

Hi,

Yes, sure, ask what do you need! 🙂