Graphing aggregation help

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.

Screenshot 2023-03-11 at 10.25.42

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

You cannot transform data with QuickSight. That would need to be done via SQL.

Depending on the data source there are many options.

For instance postgres has a crosstab function.