Set column's initial value to column's most common existing value

AppSheet AppSheet Q&A
1

What’s the best way to set a column’s initial value to the column’s most common existing value? I experimented with various approaches and ultimately implemented the following, but feel like there must be a simpler technique.

In this example, I set the initial value for the Location column in the Events table to be the Location that is already the most common location across events.

Create a “Location Count” virtual column in the Events table to count the frequency of each event’s location:

COUNT(
	SELECT(Events[Location Code], 
  [Location Code] = [_THISROW].[Location Code])
  )

For the Location column’s initial value, combine the LOOKUP and MAXROW functions to find the location with the highest count:

LOOKUP(
	MAXROW(Events, "Location Count"), 
  "Events", 
  "Event ID", 
  "Location Code"
  )
1 Like
2

ANY(Select(Events[Location Code],[Location Count]=

MAX(Select(Events[Location Count],TRUE))

))

So the MAX(Select… finds he highest count then the ANY(Select… finds the first [Location Code] which has this number. Not very elegant but should work.

Simon@1minManager.com

1 Like
3

Thanks. That seems pretty equivalent to my LOOKUP expression.

Let’s see whether other experts come up with something even better–ideally, obviating the need to maintain the virtual column in the first place.

Meanwhile, suggested a new MODE function.

4

Untested, but you could experiment with this:

ORDERBY(
  keys-list,
  COUNT(FILTER(table, ([_THISROW-1].[column] = [column]))),
    TRUE
)

Note that using a virtual column for this is going to get really expensive really fast. Consider wrapping the App formula expression for the virtual column with:

IFS(
  ("Form" = CONTEXT("ViewType")),
    ...
)
4 Likes
5

Hooray! Expression superhero @Steve does it again. Thanks a lot.

Somewhere along the way, I had tried a similar technique using [_THISROW], but hadn’t tried including the “-1”. I had assumed that [_THISROW] and [_THISROW-1] were equivalent in this context since there were only two levels of reference involved. If you have time at some point, it would be helpful to add here a conceptual explanation of your expression.

For those helped by referencing a concrete example, here’s an updated version using the Locations and Events scenario from my original post. See that I wrapped Steve’s expression in an INDEX() function to yield a single value for the column’s Initial value property rather than the list value that is the output of the ORDERBY() function.

INDEX(
  ORDERBY(
    Locations[Location Code],
    COUNT(
    	FILTER(
      	Events, 
        [Location Code] = [_THISROW-1].[Location Code], 
        )
      ), 
    TRUE
  	), 
  1)

I’ve deleted my virtual column since it’s no longer needed. Your idea to add a CONTEXT() condition to such columns is also helpful technique to keep in mind.

3 Likes
6

My expectation (again, having not tested it myself) is that [_THISROW-1] in this context refers to the row ORDERBY() is currently considering.

7

HI @dbaum , how is it that the second argument for the ORDERBY expression is a count expression?

The second argument for the ORDERBY expression is the sort-key.

sort-key - An expression that produces a sort key for the row. The sort key is compared against the corresponding sort keys of other rows to determine where the row will occur in the sorted list.

I don’t understand how a number could be a sort key.

Thanks in advance.

8

A sort key is any value that can be compared with another value of the same type to determine whether one is less-than, equal-to, or greater-than the other.

1 Like
9

Are you back ? @Steve