I have a dimension gender (example), but if there are too few rows I need to change the dimension to “too few”. Any attempt to refer to an aggregated value in my function means I can’t add this as a dimension (pie chart).
I needed a countOver by partition in the data set then
display_value()
ifelse(total_rows > 50, {gender}, ‘too few’)
I don’t think this is possible without doing it in SQL.
There’s a couple of issues that will occur.
First the countOver will make your visual dependent on the field that you are counting.
I would use sumOver(count()) like this: ifelse(sumOver(count({session_id}),[])>avg(50),‘A lot’,‘Too Few’)
Secondly, you’ll notice that I put in a string of ‘A lot’, instead of gender. In quicksight you cannot have an aggregation and display a field like {gender} that isn’t an aggregation.
So unfortunately, the calculated field I have above is the only way to do it with QuickSight calculated fields.
However, in SQL you can make a case statement that looks at a window function. That will get you what you want.
Thanks Max. Yeah I was thinking about the sql option, but really I’d like this to reflect based on the filters applied. If they narrowed down the date range - that could result in ‘too few’ at that point. The sql option could give me a count over some preset dimension.
Yeah I agree. Unfortunately you can only have it show static values when aggregating.
However, you could have the ability to change the field based on a parameter. But that would have to be manually changed. Something like having a KPI that shows the count and then depending on if it’s greater than or less than 50 you would change the parameter. I know it’s not the perfect solution but something I thought of to help the situation.
Let me mark this as a feature request to be able to reflect columns over an aggregate calculation.