Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I couldn't find a solution for my problem so I created my own Question.
I'd like to get the amount of days which expired during the period "StartDate" to "EndDate" if it was a working day.
So I got my Calendar with:
Date | Workday
1/1/2019 | 0
1/2/2019 | 0
1/3/2019 | 1
1/4/2019 | 1
1/5/2019 | 1
1/6/2019 | 0
And the Orders Table:
Order | StartDate | EndDate
A | 1/1/2019 | 1/6/2019
B | 1/3/2019 | 1/4/2019
I'd like to create a measure or a column in the orders table.
Is there something like "CountDays(From = StartDate, To = EndDate, IF ( Workday = 1))"?
Thanks in Advance!
Regards,
rohi__
Thanks for your answer Vegar!
I tried the logic behind your Code, it's working. But the performance is really bad... The code ran the whole night..
Is there another way?
Thanks for your reply!
try to crete variable and pass working days into it
networkingdays(start,end,variable)
Hi,
This is your solution. You can use IntervalMatch for this. Try the following script:
[YourData]:
Load * Inline [
Date , Workday
1/1/2019 , 0
1/2/2019 , 0
1/3/2019 , 1
1/4/2019 , 1
1/5/2019 , 1
1/6/2019 , 0
];
[Data]:
Load * Inline [
Order , StartDate , EndDate
A , 1/1/2019 , 1/6/2019
B , 1/2/2019 , 1/4/2019
];
Inner Join IntervalMatch (Date)
Load
StartDate,
EndDate
Resident Data;
Then add the following formula:
Count({$< Workday = {1}>}Workday)
Results in the following:
Jordy
Climber
Sure there is another way to solve this. Lets try using IntervalMatch(). See attached qvf
Script using intervalMatch
Result in table using different total methods
Ping: @Den
Yes, good solution @Vegar, that depends on the whishes of the rohi.
Jordy
Climber
Thank you guys @JordyWegman @Vegar for your help!
I will implement the codes soon and will mark your answers as solution!
Thanks again!
Regards