I have two data sets, and each has date info. One is ‘StartDate’, the other data set has ‘PeakDate’. Two variables have some interaction dates, but not all the same, so I created a new date, ‘WorkingDate’, to union (U) both dates by ID.
e.g)
StartDate: March 4th, 5th, 8th, and 10th
PeakDate: March 5th , 9th
WorkingDate: March: 4th, 5th, 8th, 9th and 10th
Then I want to calculate how many ‘WorkingDate’ are existing from the selected time range. I use Date Range Picker to choose date range, because I really want to visualize the date selection with a calendar.
Then I choose the range from March 1st to 10th, and ‘WorkingDate’ are only existing during 4th, 5th, 8th, 9th and 10th which causes the selection starting from 4th, not 1st. it automatically drops first, second and the third of March from date range selection as below.
How can I grab the time range(March first to 10th) from the calendar in order to calculate
In other words, how can I count the number of selected days which will be used for a denominator, and also number of working dates for numerator.
Thank you so much!
Right, it's better to be explained with dummy data. So as below is dummy.
Goal is 'what % of dates we worked during the selected date range( march 1st to march 25th) '.
Issue is that Date Range Picker automatically drops non-work date. Even though I clicked march 1st as a start date, which was not the workDate and date picker chooses march 4th the start date. So how can I count march 1st to march 25th for denominator in order to calculate count(distinct(workDate)/count(selected date range) =13/25?
workDate:
load * Inline [workDate
3/4/2021
3/5/2021
3/8/2021
3/9/2021
3/10/2021
3/11/2021
3/14/2021
3/15/2021
3/16/2021
3/20/2021
3/21/2021
3/23/2021
3/24/2021
];
Thank you!
