I created a column that I want to use as a distinct key (labeled “Key” in the screenshot) which is a concat string of multiple columns.
I’m trying to create another column in the data that counts the number of times that Key comes up in the data. I’ve tried every combination of distinct, distinctcountif, countif, etc. but can’t figure it out. I feel like it should be relatively simple…
You should be able to do distinct_count(key).
You also might want to look at distinctCountOver(key,[]).
Have you checked it in a visual?
It won’t appear in the data preview section because it is an aggregations.
Aggregations are calculated at the visual level.
So if you made a bar chart with key as the x-axis and this count as the y it should show one on the y-axis for every x-axis point.
However, if you changed the x-axis to let’s say a date then it would take the count of distinct key’s per date and calculate it then. That’s to say it aggregates it based on the group.
Hope that helps.
Thanks for the reply, Max!
I actually tried the distinct_count(Key), but ultimately, I’m trying to create a column that calculates based on another column of data (sum of units). So, I want to calculate in a separate column, for example, if the Key only comes up once in the data and there’s 10 units, I want that column to calculate 10/1 = 10.
This is the equation I ran, but Quicksight kicked back an error.
{net_units}/distinct_count({Key})
I’m assuming it is because net_units is not aggregated. You’ll need to pick an aggregation that makes sense to you but this should work.
avg({net_units})/distinct_count({Key})
