Hi all,
Wondering if anyone can help me. I have a big dataset which is two tables with a full merge. The left table is a table with one account per row and the right table is one row for each session. These can be joined on account_id for left table and patient_id on right table (see screenshot of data).
The screenshots are examplar data which demonstrates part of my problem and am hoping someone can help.
Essentially, I would like a line graph with the weeks_from_reg column on the x axis and the y axis, to be the average sessions (count of rows) grouped by weeks_from_reg.
Previously, I tried to do (with help on here): avg(count({start_time}, [{patient_id}, {weeks_from_reg}]) and I thought that worked but it looked too good to be true. What I realised was that the average was only including the people who actually did a session so of the people who used the app, they used it X times per week on average but that is not what I want. I also want to include the people who did not use the app. Looking at the screenshot, account_id 1 has used the app in the first three weeks since they registered, but person 2 didn’t use it in their second week, person 3 hasn’t used it at all and person 4 didn’t use it in their first week.
What I think I need to do is transform this data into a matrix, with account_id being the rows, weeks_from_reg as the columns and the values being the sessions per week so that I can plot that (see the second screenshot). Is anyone aware of whether I can do that in QS?
Thanks in advance
