Forum Discussion
Find Entries in Time Period
Hey,
I would like to measure a pretty easy thing, but at the moment I do not know how to start. I would like to calculate how many users haven't created any sessions in the first 30 days after they have been hired.
I have the following two tables:
Participants
| Participant_ID | Name | DepartmentCode | Hire_Date |
| 1 | John Doe | 12345 | 01-11-2017 |
| 2 | Max Mustermann | 12345 | 01-11-2017 |
| 3 | John Smith | 12345 | 01-11-2017 |
| 4 | Jan Janssen | 12345 | 01-11-2017 |
| 5 | John Blow | 123456 | 01-11-2017 |
Learning Sessions
| Participant_ID | Launch_History_id | Launch_Date | Seconds_Spend |
| 1 | 20984 | 16-11-2017 | 444 |
| 1 | 23271 | 16-11-2017 | 24 |
| 1 | 23273 | 18-11-2017 | 974 |
| 4 | 20970 | 22-11-2017 | 439 |
| 4 | 23301 | 22-11-2017 | 1 |
| 5 | 23302 | 05-12-2017 | 221 |
As a result I would expect to see one user now, user with ID 3, because he has no sessions 30 days after his hire date.
Can anyone give me a hint how to start? :smileyhappy:
Seems like you would get 2,3 and 5 as not having any sessions. Regardless, you should be able to use EXCEPT function.
5 Replies
Seems like you would get 2,3 and 5 as not having any sessions. Regardless, you should be able to use EXCEPT function.
You are absolutely right, I expect to get 2, 3 and 5.
I had a look on EXCEPT. Is my consideration right that I need to do 2 steps?
First, I will identify with EXCEPTwhich participants have no sessions at all
EXCEPT( SELECTCOLUMNS(Learning_Results_Single_Sessions_sample;"Participant_ID";Learning_Results_Single_Sessions_sample[Participant_ID]); SELECTCOLUMNS(Participants_sample;"Participant_ID";Participants_sample[Participant_ID])
Second, I will do a UNION with the Participants_ID who had no sessions in their first 30 days of employment
But I not know how to get these...
I thought about using DATESINPERIOD but still I am struggling.
I may be misunderstanding the data but I was thinking that if you did an EXCEPT on the ParticipantID columns from the two tables that you would have what you needed. But, again, I may not fully understand the entire data set.
