topic networkday() question in QlikView

networkday() question

amien — Tue, 06 Apr 2010 23:04:21 GMT

=DATE(DATE#(RIGHT(datum,8),'YYYYMMDD')-3,'D-M-YYYY')

this give me a date 3 days back .. i need 3 workdays back.

I'v read the networkdays() in the reference manual. But then i need a second date (which i dont have)

is there another option?

networkday() question

boorgura — Tue, 06 Apr 2010 23:50:17 GMT

Hey,

you can try this expression.

date(if(weekday(today()) = 'Mon' or weekday(today()) = 'Tue' or weekday(today()) = 'Wed', today()-5, today()-3))

That gives the date 3 working days previous to today. you can replace today with a date, if you have one.

networkday() question

johnw — Wed, 07 Apr 2010 00:23:51 GMT

I think this:

firstworkdate(YourDate,4)

networkday() question

amien — Wed, 07 Apr 2010 21:06:05 GMT

firstworkdate( end_date, no_of_workdays {, holiday} )

Thanks .. looks very nice .. gonna test it .. holiday can also be a table? or need they be in the expression?

(want to use this function on more then one place, and dont want to update all if needed.

networkday() question

johnw — Wed, 07 Apr 2010 21:42:47 GMT


Amien wrote:holiday can also be a table? or need they be in the expression?

I'm not sure. I've never used it. I would HOPE that you could define a field that holds all of your holidays, and then just put the field in the function, but I don't know if you can, and the examples show dates listed explicitly in the expression. If not, maybe there's some way to use concat(HolidayDate,',') to build the list, and somehow trick QlikView into doing a $ expansion or in some other way think you've explicitly listed them all.

networkday() question

amien — Thu, 08 Apr 2010 10:18:11 GMT

holidays in table is not working .. not even with CHR(39) & concat(HolidayDate, CHR(39)& ',' & CHR(39)) & CHR(39)

Guess i have to do it to hard way .. Thanks

networkday() question

johnw — Thu, 08 Apr 2010 18:36:20 GMT

There MUST be a way to do it without needing to write every single holiday date in the expression. That's just tedious.

My first thought is to do a count of holiday days in the date range and subtract them, but that can't work because you don't know the date range UNTIL you've accounted for the holiday days.

My next thought was to generate your own "function" to do it from your holiday table. OK, that seems to work. Build the function like this:

[Holidays]:
LOAD * INLINE [
Holiday
3/15/2010
3/10/2010
];
SET customfirstworkdate = 'firstworkdate($1,$2';
FOR I = 0 TO noofrows('Holidays')-1
LET customfirstworkdate = customfirstworkdate & ',' & num(peek('Holiday',I,'Holidays'));
NEXT I
LET customfirstworkdate = customfirstworkdate & ')';

And then use it like this:

$(customfirstworkdate(EndDate,NoOfWorkdays))

See attached.

There might be a better way. But at least you won't have to manually maintain the list of holidays. You can just load it from the normal source of the data.

networkday() question

amien — Mon, 12 Apr 2010 21:47:19 GMT

John,

=$(customnetworkdays('1-1-2010','1-2-2010'))

this works .. but this doesnt:

=$(customnetworkdays('1-1-2010',DATE(DATE#(MIN(datevalue),'YYYYMMDD'),'D-M-YYYY')))

networkday() question

johnw — Mon, 12 Apr 2010 21:57:15 GMT

When you're providing values for the parameters, it's using commas as the separator. Since it is a dollar sign expansion, that part is occurring before anything else. So I believe your second parameter is getting prematurely cut off right before the formatting, which would keep it from working.

I don't THINK you need to keep a consistent format for the networkdays function, as long as it's a date or a number that can be interpreted as a date. So the easy solution would be to remove the formatting and just use this:

$(customnetworkdays('1-1-2010',min(datevalue)))

Does that work? If not, I can add a customnetworkdays "function" to my sample file and try to figure out better what's going on.

networkday() question

amien — Mon, 12 Apr 2010 22:40:02 GMT

that gives output .. but a very big number .. nothing that i need.

i thought that date conversion was needed because i'v set: SET DateFormat='D-M-YYYY'

also tried to get the date correct using LEFT/MID/RIGHT .. no result.

also tried to convert the date in the load script ($1 and $2).. also not working

networkday() question

amien — Mon, 12 Apr 2010 22:42:15 GMT

customfirstworkdate is basicly the same as customnetworkdays.. only


SET customnetworkdays = 'networkdays($1,$2';
FOR I = 0 TO noofrows('Holidays')-1
 LET customnetworkdays = customnetworkdays & ',' & num(peek('Holiday',I,'Holidays'));
NEXT I
LET customnetworkdays = customnetworkdays & ')';

networkday() question

johnw — Mon, 12 Apr 2010 23:29:59 GMT

OK, looks like at least my explanation of the problem appears to have been correct. When I build your new "function" and use it, it does indeed hack off the expression right before the formatting.

However, removing the formatting like I suggested fixes it in my file. I built the function, and I'm doing this:

$(customnetworkdays('1/1/2010',min(Date)))

It's returning 46 for a min(Date) of March 5, 2008, which is before any of my custom holidays. Seems like the right number. I'm going to add in Jan 1 and Feb 15 as holidays. The number drops to 44, as expected.

So... I guess I don't know what's going wrong in your version. Attached is my updated file. Hopefully you can figure it out from there. If not, can you post a sample file demonstrating the problem?

networkday() question

amien — Tue, 13 Apr 2010 08:24:36 GMT

i'm working with this:

[Dates]:
LOAD * INLINE [
Date
20100305
20100312
20100317
];

When [Dates] have the correct notation .. then its working.

networkday() question

amien — Tue, 13 Apr 2010 08:24:38 GMT

networkday() question

amien — Wed, 14 Apr 2010 08:35:17 GMT

John,

i added the DATE(Date#) as additional field in my load script .. works fine .. thanks!