topic need help on data-model in QlikView

need help on data-model

Tue, 21 Jun 2016 13:15:36 GMT

Hi all,

this is my first post on this amazing forum. I've already learned a lot from you all, but now I've got a specific case in which I can use your help to setup data-model.

I've got a table called "Orders" with several kinds of date-fields:

example:

Order nr	Date entered	Date confirmed to client	Date send to production	Date fully packed	Date send to client
321456	29/04/2016	02/05/2016	02/05/2016	19/05/2016	20/05/2016
321457	02/05/2016	03/05/2016	03/05/2016	10/05/2016	10/05/2016
...

and a table called "Working Days"

example:

Date	Is Workday?
29/04/2016	0
30/04/2016	1
01/05/2016	0
02/05/2016	1
03/05/2016	1
04/05/2016	1
05/05/2016	0
06/05/2016	0
07/05/2016	0
08/05/2016	0
09/05/2016	1
10/05/2016	1
11/05/2016	1
12/05/2016	1
13/05/2016	1
14/05/2016	0
15/05/2016	0
16/05/2016	1
17/05/2016	1

I want to make a QV-app as follow:

We need to filter the data on one of the possible dates:

for example:

All orders that have been entered in April 2016 : [Date Entered] between 01/04/2016 and 30/04/2016

or All orders that have been send to production during May 2016: [Date send to production] between 01/05/2016 and 31/052016

After this filtering, I need to select 2 kind of dates to calculate the number of working days between it.

in other words: I want to filter orders based on [Date entered] between 2 selected dates and show the number of working days between for example [Date Send to production] and [Date fully packed]

another example can be:

Show me the number of working days between [Date fully packed] and [Date send to client] for all orders that have been send to production in June 2016

result shoud be:

# working days	# orders	% tot	Cumul #	cumul %
<= 1WD	100	10 %	100	10 %
<= 2WD	250	25 %	350	35 %
<= 3 WD	50	5 %	400	40 %
<= 4 WD	500	50%	900	90%
>= 5WD	100	10%	1000	100%

This tells us that 40% of the selected orders has been produced in less than 3 working days.

Who can give me some advize on how to setup this data-model to easily get result as above.

any tip is welcome !

thanks in advance

Re: need help on data-model

swuehl — Tue, 21 Jun 2016 20:08:44 GMT

If you want to use different dates in a common timeline / dimension, a common approach would be to create a canonical date:

Canonical Date

But it may also be useful to keep the date fields separated and linked to their own master calender:

Tutorial - Using Common Date Dimensions and Shared Calendars

Re: need help on data-model

Wed, 22 Jun 2016 06:12:59 GMT

@sweuhl: thanks for your reply - I will take a look at the mentioned articles. hopefully I can find the solution

Re: need help on data-model

Colin-Albert — Wed, 22 Jun 2016 06:24:44 GMT

Have a look at the NetWorkDays() function as this may help in counting the number of working days.

function:networkday

Re: need help on data-model

Wed, 22 Jun 2016 06:45:43 GMT

Thx for your reply.

I know the function NetworkDays(), but main-problem is not that.

Main-problem is how to setup data-model to make selections on different dates.

The answer of Swuehl seems to be right, but first I want to read and test it.