Issue with MakeDate / Network Days

[#1]

I am a Qlikview novice I am having an issue with the following code. I create 2 dates. One is specified from a date I have hard coded (dteToday). The second is a date that is composed from a calendar file I have created. I have checked the contents of the variables vYear (2010) ,vMonth(2) and vDay(1) in the"Variable Overview". The issue is that when the code has executed the value of vDaysIntoPeriod is 28728 !! I was expecting a much smaller as the dates are within the same month.

SET dteToday = makedate( 2010,2,10 );

LOAD min(FinanceYearPeriod), min(Date) as MinDate RESIDENT Calendar (qvd)
WHERE FinanceYearPeriod =$(vFinanceYearPeriod) GROUP BY 1;

LET vPeriodStartDate = peek('MinDate');
drop table PeriodStartDate;

LET vYear = num(mid(vPeriodStartDate,1,4));
LET vMonth = num(mid(vPeriodStartDate,5,2));
Let vDay = num(mid(vPeriodStartDate,7,2));
Let vStart = makedate(vYear, vMonth, vDay);
Let vDaysIntoPeriod = networkdays($(vStart),$(dteToday));

Thanks in advance.

Paul

[michael_anthony · Accepted solution]

Paul,

vDaysIntoPeriod is trying to evaluate = NetWorkDays(01/02/2010,MakeDate(2010,2,1)). It's treating vStart as a number and so returning a strange result.

Instead try Let vDaysIntoPeriod = NetWorkdays('$(vStart)',$(dteToday));

The ' ' means it puts in a recognisable date value rather than a calculated number. Don't put the ' ' around $(dteToday) as it is Set and so substitutes in the MakeDate function which evaluates to 1st Feb.

[#3]

Michael,

Many thanks for your solution it worked perfectly first time. I did not realise there was this difference between SET and LET.

Kind Regards,

Paul