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.
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”
)
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)
)
Try:
ANY(SORT(SPLIT([Weekday], ","), TRUE))
@Steve: Thanks so much. This appears to be working for me now.
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:
I’m wondering if there is a way to simplify this.
@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…
User | Count |
---|---|
17 | |
12 | |
5 | |
5 | |
5 |