[Feature request + workaround] Mode function

Q&A
, , , ,
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
)