Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I want to group participant's attendance related data in to date ranges.
Assume my original data set as below,
Name | Date |
Mark | 10/07/2018 |
David | 18/08/2018 |
Alan | 19/08/2018 |
Simon | 21/08/2018 |
Peter | 13/07/2018 |
Alan | 13/08/2018 |
Marcus | 12/07/2018 |
Brenden | 22/08/2018 |
Nick | 15/08/2018 |
Tim | 16/07/2018 |
Brian | 20/08/2018 |
Wisla | 3/09/2018 |
Tom | 26/08/2018 |
Andrew | 5/09/2018 |
Thomas | 10/09/2018 |
I want to base my grouping of my participants based on the below group list,
Date Ranges |
04/07/2018 - 18/07/2018 |
19/07/2018 - 01/08/2018 |
02/08/2018 - 16/08/2018 |
17/08/2018 - 31/08/2018 |
01/09/2018 - 15/09/2018 |
The final outcome of the problem should be something like below,
Date Range | Count of Participants |
04/07/2018 - 18/07/2018 | 4 |
19/07/2018 - 01/08/2018 | 0 |
02/08/2018 - 16/08/2018 | 2 |
17/08/2018 - 31/08/2018 | 6 |
01/09/2018 - 15/09/2018 | 3 |
My requirement is to add a new dimension during the data load process to count the grouped participants count.
Since the date range will gradually increase over the time and above I have created a data set only for a selected months. However, the data set will have a minimum date and a maximum date at any given time period. Thus, I am planning to use ApplyMap() function to map the date field against the date range.
I have also attached the data sheet for your easy reference.
Appreciate if someone could help me with this regards.
Thanks in advance.
Kind regards,
Andy
Hi Pradeept,
Thanks for your valuable prompt reply.
However, when I run the script, the outcome looks something like below,
Date Ranges | =Count(Name) |
01/09/2018 - 15/09/2018 | 1 |
02/08/2018 - 16/08/2018 | 1 |
04/07/2018 - 18/07/2018 | 2 |
17/08/2018 - 31/08/2018 | 2 |
19/07/2018 - 01/08/2018 | 0 |
- | 2 |
Please find the attached screenshot I got for the above table.
It is slightly different from the expected outcome and appreciate your feedback on this.
Thank you very much.
Kind regards,
Andy