Find Entries in Time Period

[theitguy]

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?

[Greg_Deckler · Accepted solution]

Seems like you would get 2,3 and 5 as not having any sessions. Regardless, you should be able to use EXCEPT function.

[theitguy]

@Greg_Deckler

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.

[Greg_Deckler]

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.

[theitguy]

Hey @Greg_Deckler,

I gave it another thought and I think you are right. I guess, I had a wrong mental model.

[theitguy]

 Hey Greg @Greg_Deckler,

I needed some time to think about it. I have also changed the data a bit to make it more clear.

What I want is to select a point in time (via a date slicer) and then I want to see the amount of leaners who have made progress in their first 30 days. Given the amount of all learners, I can then calculate the percentage. The same I am wanna do for 60 and 90 days after their hire date.

So, if I check the first 30 days, I would expect to get 2 (Participant 1 and 4), for 60 days I would expect to get 3.

Maybe this is going into the same direction as Set-FILTER-value-with-variable