topic Networkdays Function in QlikView

Networkdays Function

Wed, 04 Apr 2012 18:16:54 GMT

Hey guys,

So I am trying to create a field in the script that is the # of days a task was open. Please review the following:

Tasks:

Load

%Task_ID,

Task_Start_Date,

Task_Compl_Date

From [Data Source]

;

Left Join

Load

%Task_ID,

NETWORKDAYS(Task_Start_Date, Task_Compl_Date) as DaysOpen

Resident Tasks

;

What I get is a number for each unique set of dates but when a date repeats, like 2 different tasks that were completed on the same date, I only get a value for one and null for the other. This is because it's only doing the function for unique sets of dates. Example:

Task_Start_Date Task_Compl_Date DaysOpen

01-02-2012 01-03-2012 2

12-23-2011 01-03-2012 NULL

Im sure it's simple but I'm not sure how to correct this.

Thanks for any and all help.

Networkdays Function

m_woolf — Wed, 04 Apr 2012 18:32:45 GMT

Include the task ID in your chart.

Networkdays Function

Wed, 04 Apr 2012 18:36:49 GMT

I should have included %Task_ID in my example, sorry. It doesn't help though, becuase the script is simply not creating records when there is a repeated date.

Networkdays Function

m_woolf — Wed, 04 Apr 2012 18:40:54 GMT

Try without the join load:

Tasks:

Load

%Task_ID,

Task_Start_Date,

Task_Compl_Date,

NETWORKDAYS(Task_Start_Date, Task_Compl_Date) as DaysOpen

From [Data Source]

;

Networkdays Function

Wed, 04 Apr 2012 20:33:38 GMT

I am only doing the join load because the date I am using (Copletion date) is actually being created in the intial load. So what I did is Instead of only doing %Task_ID and the Networkdays function I did this:

Tasks2:

Load *,

NETWORKDAYS(Task_Start_Date, Task_Compl_Date) as DaysOpen

Resident Tasks

;

Drop Table Tasks; //Dropped the original

and it seems to be working hoever the averages are not correct which I believe may be a seperate problem altogether.