Can someone help transfer the table below to a frequency table

[mmmzzss]

Hi there,
I have a question about how to convert the table below to the frequency table below:

one year ago	two years ago	three years ago	four years ago	five years ago
5 times or more	5 times or more	5 times or more	5 times or more	5 times or more
1 time	5 times or more	3 times	5 times or more	5 times or more
5 times or more	5 times or more	5 times or more	5 times or more	5 times or more
5 times or more	5 times or more	5 times or more	5 times or more	5 times or more

freq	one year ago	two years ago	three years ago	four years ago	five years ago
1 time	1	0	0	0	0
2 times	0	0	0	0	0
3 times	0	0	1	0	0
4 times	0	0	0	0	0
5 times or more	3	4	3	4	4

I was trying to drag the 1yrago,2 yrsago,… into the values and group by 1yrago, but it turns out all columns are same. Could you help me out on this one?

Thanks

[duncan]

Hello @mmmzzss !

Have you tried using a pivot table instead? Also, what are the data types of the fields that you are using to create the table?

Using pivot tables - Amazon QuickSight

[mmmzzss]

Hi duncan,

It was the frequency like 1,2,3,… (number) and I used case when query to categorize it into 1 time, 2 times,…(text); For now, I’d like to count how many 1 time/2times/3times… have in column 1 year ago, 2 years ago…

[duncan]

@mmmzzss I would definitely give the pivot table a try for this scenario.

Let me know if it helps!

[mmmzzss]

I tried pivot table before. However, the columns are all the same when I chose to one of the columns as ‘Rows’.

[duncan]

You’ll want to make sure that the aggregate on the field wells are correct to the type of output that you are trying to measure.

I was able to recreate something similar with a test visualization:

This one is close to what I beleive you are looking for and I’m making sure that the Values field well is showing me a distinct count

When I change Values field well aggregate to sum, it will change the output in the cells