Generating a List of Inactive Users

Hello QS community,

I have a live data base which has say,100 students. Whenever a student does a test in a week, data for this student is generated and I am able to view his/her score through the dashboards that I create.

I need to be able to generate a list of all the students who have not yet participated in a particular week so that teachers can use this list to nudge the students to complete their work.

What can I do to generate such a list?

PS: My current makeshift solution is to crate a pivot table and select a longer time period, this shows all students who have participated at least once in that time period. The teacher then checks the relevant week and takes action. Unfortunately this is not a user friendly solution and the use/understanding of teachers is low.

Hi @CGauthor,

Here is how you can solve it using QuickSight:

  1. Create a new QuickSight dataset with the full list of all students.
  2. Use a Left Join between the full student list above and the test dataset you already have. If you are not sure how to do the Join, read this article.
  3. Now, you can notice that all the columns from the test dataset are expanded. Pick one of the columns that can never have nulls in the original test dataset. For example, a Test ID. You can see that this column will be empty for student who didn’t participate in any test. This is your bingo :slight_smile:
  4. Now, in the dataset, you can apply a filter on the Test ID column and keep only rows with null values in Test ID. Publish the new dataset and include it in your current Analysis as the second dataset to present visuals with students that didn’t test.
  5. If you need to show side by side on the same visual, both tested and untested students, you can ignore step 4, and instead use the new dataset without the filter as your main dataset for your dashboard. In such case, you will need to make sure you handle the null values correctly in various visuals. You can create also create calculated fields in the Dataset or Analysis that will count the number of students that attended tests by using this formula:
distinct_countIf({Student ID}, isNotNull({Test ID}))

To count students that didn’t had any test:

distinct_countIf({Student ID}, isNull({Test ID}))

I hope it helps.

Hi, @CGauthor. Did @Gil_Raviv’s solution work for you? I am marking his reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!