Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
If you want to use different dates in a common timeline / dimension, a common approach would be to create a 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
@sweuhl: thanks for your reply - I will take a look at the mentioned articles. hopefully I can find the solution
Have a look at the NetWorkDays() function as this may help in counting the number of working days.
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.