topic networkdays() forecast in QlikView

networkdays() forecast

Tue, 22 Jun 2010 18:48:48 GMT

Hello everybody,

I have a question regarding the networkdays() function.

I have data with dates up until 11-06-2010 as field [DATE PICKED], but what I would like to do is predict the amount of working days that will apply for months July up until December. The reason I cannot do this is because I use networkdays(montstart([DATE PICKED]),monthend([DATE PICKED])) to calculate the working days in order to show it correctly by month. I have historical data, but only want my application to show the current year's networkdays.

Could anyone possible help me or is the only option to use a pre-calculated sheet & link the data??

Thank you. All repsonses are appreciated 🙂

networkdays() forecast

Anonymous — Tue, 22 Jun 2010 21:26:29 GMT

You don't need to have all dates listed in order to use networkdays function. For example, if you have
networkdays('07/01/2010', '12/31/2010')
You get the result for the second half of year 2010 regardless if you have all these dates anywhere in you data. Another example, for full curent year:
networkdays(YearStart(today()), YearEnd(today()))

networkdays() forecast

Wed, 23 Jun 2010 05:38:00 GMT

networkdays() funcation given number of working day between two date excluding Saturday and sumday.

Regards

Ashish

networkdays() forecast

Wed, 23 Jun 2010 12:39:25 GMT

Hi,

First of all thank you for your responses.

MIchael - I only use the Month name in my calculation, thus whenever I do not click on a year, it shows the networkdays for the previous year (2009). The reason I don't want to use Year as a dimension is because I want to list the valid picks for this year next to the valid picks of last year for each corespondig month.

Without listing Year as dimension I've tried using your logic, but it then shows the same amount of networkdays accross all the months. If I do add the Year to the dimension, I still don't get the forecasted network days.

Could you be so kind as to have a look at the attached file & leave me comments regarding this??

Thank you for your help guys!!

networkdays() forecast

Anonymous — Wed, 23 Jun 2010 14:09:49 GMT

Sorry, I don't download attachments, with very few exceptions.
Example of your problem would be helpful. For now, I assume that you select a month (hope it is numeric or dual), and want to see the number of working days between the end of the selected month and the year end for both current and previous year. If this is correct, the right question to ask is how to define start date and end date in the newtworkdays() function.

For the current year:
startdate - makedate(year(today()),MONTH+1,1)
enddate - makedate(year(today()),12,31) or YearEnd(today())
networkdays(makedate(year(today()),MONTH+1,1), YearEnd(today()))

For the previous year:
startdate - makedate(year(today())-1,MONTH+1,1)
enddate - makedate(year(today())-1,12,31) or date(YearStart(today())-1)
networkdays(makedate(year(today()),MONTH+1,1), date(YearStart(today())-1))

networkdays() forecast

Wed, 23 Jun 2010 16:25:23 GMT

Hi again,

Here are some images of what I`m trying to achieve & what happens:

I`d like to show the 2009 Picks & Picks together (this is why I did not add YEAR as a dimension, as my columns would double) -the first two columns have been sorted out using set analysis - . When no year/month is selected, then it the networkdays() function will show the working days of 2009, but I want it to show 2010's working days. When a year has been selected, it only shows the networkdays up until June, but I would like to show it up until December, because I have addisional calculations that I would like to do with the number of working days (hence the forecast).

If this is not explicitly possible, could you help me with altenatives that might work?

networkdays() forecast

Wed, 23 Jun 2010 19:47:44 GMT

This might be a kluge, but I've got it working. I assume that you always want working days to refelect the number of working days for the current year. Perhaps I'm wrong. Anyhow, I changed the formula you're using to calculate working days like so:


=max({1<YEAR>} networkdays(makeDate(year(today()),MONTH),monthend(makeDate(year(today()),MONTH))))

Now, regardless the year selection, I always see the number of working days in the current year.

networkdays() forecast

Anonymous — Wed, 23 Jun 2010 20:22:35 GMT

You have MONTH as dimension - that means you know start and end date for 2010:
startdate - makedate(2010, MONTH,1)
enddate - monthend(makedate(2010,MONTH,1))
So, working days will be:
networkdays(makedate(2010, MONTH,1), monthend(makedate(2010,MONTH,1)))

Again, the MONTH field here must be numeric or dual.

PS: suggestion from Fry is logically the same, just different syntax and 2010 replaced with latest year. Use whatever fits you better.

networkdays() forecast

Thu, 24 Jun 2010 11:06:25 GMT

Hi,

Thank you both very much for your responses. I've tried your solutions & now understand better on how to approach such a problem. Here is the syntax that i had used

=max({1<YEAR=>} networkdays(makeDate(year(today()),MONTH),monthend(makeDate(year(today()),MONTH))))

One thing I still don't understand though is this:

={1<YEAR>}

What exactly is the '1' for??