Expression needed to find lowest [DSO]but show the corresponding [Division] based on [Date]= Today()

AppSheet AppSheet Q&A
1

I need an expression to find the lowest [DSO] if [date] = TODAY(). I want to show the corresponding [Division] of that lowest [DSO] value. Here is my table:

Tiger1_0-1683308855938.png
Tiger1_0-1683308855938.png395Ă—298 6.77 KB

2

There could be some approaches. One could be based on MINROW() expression.

The other is as follows

Please create a slice called say “Todays Records” on the table with a slice filter expression such as [Date]=TODAY()

Then the expression for the Division with the lowest DSO value for TODAY() can be

ANY(SELECT( Todays Records[Division], [DSO]= INDEX(SORT(Todays Records[DSO], FALSE),1)))

Where 'Todays Records" is the slice with [Date]=TODAY() expression.

Not tested. Please test well.

3

Thank you. I wanted this in one expression - so no slices. Reason - I am trying to place it in one of my Word doc templates.

4

I currently have this but it doesn’t work:

SELECT(DSO for all divisions[Division],
AND(
[Date] = TODAY(),
MIN(DSO for all divisions[DSO])
)
)

5

Thank you for details.

The basic syntax issue with your expression is you cannot simply mention MIN(DSO for all divisions[DSO])in the AND() expression. It will need to be something like [DSO] =MIN(DSO for all divisions[DSO]) because AND() needs subexpressions to evaluate as TRUE or FALSE.

Second syntax issue is select() will return a list, so the expression needs to be wrapped with ANY() to get a single value.

The third and major logical issue with your expression is the part of the expression MIN(DSO for all divisions[DSO]) will give the minimum DSO value for the entire table/slice , meaning for all dates and not just today’s date. That is why I requested you to create a slice for [Date]= TODAY().

So your expression could be modified to the following expression

ANY(SELECT(DSO for all divisions[Division],
AND(
[Date] = TODAY(),
[DSO]=MIN(SELECT(DSO for all divisions[DSO], [Date]=TODAY()))
)
))

However, even though the above expression will work, it has a SELECT() in another SELECT() that could be sync time / evaluation time expensive. If the table size is not very large , you could go for it.

You may want to try my suggested slice based approach. I have tested that it works. Even if you are using the expression in a template, slices can be used in a template.

Edit:

Also my above suggested expression can be further simplified as follows :slightly_smiling_face: . Sorry for sharing a bit longish expression earlier.

ANY(SELECT( Todays Records[Division], [DSO]= MIN (Todays Records[DSO])))

1 Like
6

Thank you very much this DID work. Thanks again!

1 Like