Hello. This post is a feature request for a mode (most common value) aggregation function. I spent the last several hours working on the solution I will present below, but I could have saved a lot of time if this function were implemented. Also, this solution does not work in the general case, so we definitely still need the mode function.
Yes I found this thread and while it was somewhat helpful in leading me to how to think about mode, it actually offers an incorrect answer. The thread “solution” only gives the number of times the mode occurs in a set of numbers. Not the actual number itself- which is the relevant piece of info.
Also this thread is just the feature request without mentioning the workaround(s). So I wanted to document my solution for those who go looking.
Additionally, I want to add a note about why I had to do this the way I did it. I don’t have direct access to the database, and can’t do any preprocessing of the data before it gets into Quicksight. We are a client of a PoS solution that only recently started granting clients author access in Quicksight. The tables I get are the tables I get.
That said, here is the problem I set out to solve. We have a table of customer checkins to our business. The relevant fields look something like this:
What I wanted to do was calculate the most common day of the week (Sunday, Monday, etc.) a customer comes in on. To do this, I constructed the following calculated fields. I know some of this could probably be combined but I did things step by step so I could check everything along the way:
A pretty basic extract to get the weekday out of the checkin date.
This is more or less the answer from the above linked thread.
This is just the one for Sunday, but I made one of these calculated fields for every day of the week. Just the “dayOfWeek=1” changed between them. Also, the reason we are taking the max is so we can do math with the calculated field directly above without getting an aggregation mismatch error.
And finally, this behemoth:
It’s ugly and slow but it gets the job done. Also, this only really works when the range of data is really low. Anything more than a handful of possible values and your required calculated fields and ifelse stack gets out of control. Would love a more seasoned Quicksight guru to show me a better workaround, if they’ve got one. I’ve only been doing this a month or so, and have a lot to learn.