[Feature request + workaround] Mode function

Q&A
, , , ,
1

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:

image
image787×412 18.6 KB

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:

image
image367×129 3.7 KB

A pretty basic extract to get the weekday out of the checkin date.

image
image930×143 6.71 KB

This is more or less the answer from the above linked thread.

image
image965×165 5.73 KB

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:

image
image652×689 22.1 KB

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.

2 Likes
2

Hello @B_Burgess , welcome to the Quick Sight community!

I can definitely see the need for a dedicated mode function and seems like this has been asked for quite a few times.

That being said, I think there could be an easier way to do this. If you do a count of days by the ID then do a dense rank of of the values by weekday you should get your most common weekday.

Can you try something like this:
WeekdayCount = count({dayOfWeek}, [id])

RankedDay = denseRank([max(WeekdayCount) DESC], [dayOfWeek, id])

Let me know if you hit any errors. You could also add some sample data to an arena and we could troubleshoot there.

Also, for your ifelse statement, you don’t need to repeat the “ifelse” on every row. You could do the following:

ifelse(
    {preferredDayofWeek} - {sundayCount} = 0, 
        "Sunday",
    {preferredDayofWeek} - {saturdayCount} = 0,
        "Saturday",
    {preferredDayofWeek} - {fridayCount} = 0,
        "Friday",
    etc...,
    NULL
)
3

Hi @B_Burgess,
It’s been awhile since we last heard from you on this thread so following up; while this has already been marked as a feature request, wanted to see if you had any additional questions or comments to add?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you

4

Hi Duncan. Thank you for offering a different solution! This is quite a bit cleaner, I just had to make a couple changes to get it working. I am working at the dataset level so it may have impacted how I need to use certain functions, but the above calculations as written gave errors.

Instead here is what I did:

weekdayCount = countOver({dayOfWeek},[{dayOfWeek},{id}],PRE_FILTER)

rankedDay = denseRank([max({weekdayCount}) DESC], [id])

I had never used rank functions before so this was a pretty handy thing to learn about. This is also much more generalizable. Thanks again for the alternate solution!

2 Likes