Identifying the Max value in a list of numbers

AppSheet AppSheet Q&A
1

Hi I have a column called [Weekday] (Type = ‘Text’) which contains an App Formula that returns a list of WEEKDAY() values based on a date range ([From Date] & [To Date]) entered by the user
e.g. [From Date] = 19/10/2020, [To Date]=23/10/2020, [Weekday]=2,3,4,5,6

Using this example what expression would I use to identify that 6 is the highest number in the result for [Weekday]?

Many thanks.

2

Since [Weekday] is a text type, you won’t be able to use the MAX() expression on it. Is [Weekday] a real or virtual column? What expression do you use to generate the value in [Weekday]? You could possibly convert that column to an EnumList base-type Number if it is a real column. Or if it is virtual, it can just be a List type.

Or you could do it the “ugly” way.

IFS(> CONTAINS( [weekday] , “7” ) , “7” ,> CONTAINS( [weekday] , “6” ) , “6” ,> …> CONTAINS( [weekday] , “1” ) , “1”> )

3

Hi @Marc_Dillon. Thank you for that.

[Weekday] is a real column.

I have used your suggestion of converting the type to EnumList and base type Number.
My expression is

IF(
OR(
[Type]=“Production”,
[Type]=“Non Production”),

TOP (
LIST (
WEEKDAY([Date From]),
WEEKDAY([Date From] +1),
WEEKDAY([Date From] +2),
WEEKDAY([Date From] +3),
WEEKDAY([Date From] +4),
WEEKDAY([Date From] +5),
WEEKDAY([Date From] +6)),
HOUR ([Date To] +1 - [Date From]) / 24),LIST(“”))

but when I use the following expression on a subsequent column I am getting the following error ‘The inputs for function ‘MAX’ should be a list of numeric values’

IFS(

MAX(Resource_Planner_Support_Master[Weekday])=2,
SORT(
SELECT(Work_Pattern[Monday],TRUE,TRUE)-SPLIT(SELECT(Resource_Planner_Support_Master[Name],
AND(
[_THISROW].[Date From]>=[Date From],
[_THISROW].[Date To]<=[Date To])), “,”),FALSE)
)

4

Try:

ANY(SORT(SPLIT([Weekday], ","), TRUE))
2 Likes
5

Good call @Steve. Even though MAX can’t be run on a list of text, they can still be sorted, then you just pick the first.

@MauriceWhelan I’m interested in what you’re accomplishing here:

MauriceWhelan:

TOP (> LIST (> WEEKDAY([Date From]),> WEEKDAY([Date From] +1),> WEEKDAY([Date From] +2),> WEEKDAY([Date From] +3),> WEEKDAY([Date From] +4),> WEEKDAY([Date From] +5),> WEEKDAY([Date From] +6)),> HOUR ([Date To] +1 - [Date From]) / 24)

I’m wondering if there is a way to simplify this.

1 Like
6

@Steve: Thanks so much. This appears to be working for me now.

1 Like
7

@Marc_Dillon: I am using

TOP (
LIST (
WEEKDAY([Date From]),
WEEKDAY([Date From] +1),
WEEKDAY([Date From] +2),
WEEKDAY([Date From] +3),
WEEKDAY([Date From] +4),
WEEKDAY([Date From] +5),
WEEKDAY([Date From] +6)),
HOUR ([Date To] +1 - [Date From]) / 24)

to create a list of [Weekday] Numbers (based on a date range) which I can then use one of the values of to return the relevant list of [Name]s from the Work_Pattern table which contains columns [Monday], [Tuesday], [Wednesday] etc…