need help on data-model - Qlik Community

Report Inappropriate Content · ‎2016-06-21

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

swuehl · ‎2016-06-21

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