Question

How can I create a measure which provides the statistical mode?

  • 23 February 2021
  • 1 reply
  • 27 views

I want to create a measure which will calculate the statistical mode for a given value.

 

The SQL equivalent would be:

SELECT TOP 1 Column_NameFROM   Table_nameGROUP  BY [Column_Name]ORDER  BY COUNT(*) DESC

 

I can see how to get this value while looking at data in an Explore, but given a model with dimension DIM and Measure M, I want to be able to write a second measure that is something like this:

 

measure: modal_M {

  type: number

  sql:  STUFF GOES HERE ;;

}

 

When viewed in an explore, modal_M would provide the modal value of M, which could be sliced by DIM or any other dimension.

 

Example output:

Country  |  Modal_FirstName_Length
USA  |  8
Canada  |  12
Mexico  |  11


This topic has been closed for comments

1 reply

Userlevel 6
Badge +1

You can’t do it in one measure because it requires grouping. As your SQL states you’d have to group your whole table first, in order to get the TOP 1. It’s easy in the Explore because you achieve the aggregation via usual query and then you can use Table Calcs or even visualisation settings with sorting (Show First 1) in order to show only one row.


The only way for you to achieve that would be to create PDT in Looker that gets the mode and CROSS JOIN it to your explore so it’s always available