Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

need help on data-model

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 nrDate enteredDate confirmed to clientDate send to productionDate fully packedDate send to client
32145629/04/201602/05/201602/05/201619/05/201620/05/2016
32145702/05/201603/05/201603/05/201610/05/201610/05/2016
...

and a table called "Working Days"

example:

DateIs Workday?
29/04/20160
30/04/20161
01/05/20160
02/05/20161
03/05/20161
04/05/20161
05/05/20160
06/05/20160
07/05/20160
08/05/20160
09/05/20161
10/05/20161
11/05/20161
12/05/20161
13/05/20161
14/05/20160
15/05/20160
16/05/20161
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% totCumul #cumul %
<= 1WD10010 %10010 %
<= 2WD25025 %35035 %
<= 3 WD505 %40040 %
<= 4 WD50050%90090%
>= 5WD10010%1000100%

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

4 Replies
Highlighted
MVP
MVP

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

see also

How to use - Master-Calendar and Date-Values

Highlighted
Not applicable

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

Highlighted

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

function:networkday

Highlighted
Not applicable

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.