I have a dataset containing responses to questions in quizzes. I want a visual to show each user’s response to every question in a quiz. As a flat table, that would look like this:
However, the requirement is for each row to show one user’s attempt at the quiz. This means I need to pivot my data with the “Question” field as my column and “Response” as my value.
The problem is that the only aggregations allowed on a text field in QuickSight are count and count distinct. I can’t pivot my data in SQL because the number of questions in a quiz is not fixed. The dataset contains different quizzes and a quiz could have any number of questions.
Something like this is very straightforward in Tableau and Power BI but not possible in QuickSight today.
The LastValue function in QuickSight doesn’t give the correct result.
It requires me to include the Response field as a row or column in my pivot table. It adds extra columns to my pivot table. Hiding just hides the headers and doesn’t actually remove the extra columns.
Feature request:
One possible solution would be to allow unaggregated text values in a pivot table, or if only aggregated values are allowed, to allow min or max on a text field.