Slice with daily min-value + daily max-value

[Asheetbogo]

i have a table with serveral datasets (rows) for each day

i need a slice with 2 datasests (rows) for each day:
a) 1 row with min-value of a specific numeric field for each day and
b) 1 row with max-value of the same numeric field for each day

i know that i can achieve Min- and Max-vaues with expression: MAXROW() and MINROW()

but i dont find a solution for selecting only 1 max-row and 1 min-row for each day …

many thanks in advance for your help.

Gottfried SUDI

[Suvrutt_Gurjar]

Asheetbogo:

but i dont find a solution for selecting only 1 max-row and 1 min-row for each day …

Could you elaborate on why you mentioned so? MAXROW() and MINROW() always return one row based on the arguments mentioned in those expressions.

[Asheetbogo]

hello Suvrutt_Gurjar,

thank you very much for your prompt reply!

i found out that MAXROW() and MINROW() only returns 1 dataset (row) for my hole “base-table” …

i need daily MAXROW()s and MINROW()s in my resulting slice
that means i need 2 datasets (rows) for each day in my slice (1 with min-value and 1 with max-value) …

[Suvrutt_Gurjar]

Okay, got it. Thank you. Your slice expression could be something like

[Table key]= OR( MAXROW( … ), MINROW(…) )

[Asheetbogo]

as i can see, this expression will only return two rows for the “hole” base-table …

i need 2 daily rows (max/min) for each day of the datasets of the basetable in my slice …

e.g.:
base-table: slice:
09.01.2022: 10 datasets (rows) 09.01.2022: 2 datasets (1 min + 1 max)
08.01.2022: 8 datasets (rows) 08.01.2022: 2 datasets (1 min + 1 max)
…

[Asheetbogo]

as i can see, this expression will only return two rows for the “hole” base-table …

i need 2 daily rows (max/min) for each day of the datasets of the basetable in my slice …

e.g.:
base-table:
09.01.2022: 10 datasets (rows)
08.01.2022: 8 datasets (rows)

slice:
09.01.2022: 2 datasets (1 min + 1 max)
08.01.2022: 2 datasets (1 min + 1 max)
…

[Suvrutt_Gurjar]

Asheetbogo:

e.g.:
base-table:
09.01.2022: 10 datasets (rows)
08.01.2022: 8 datasets (rows)

slice:
09.01.2022: 2 datasets (1 min + 1 max)
08.01.2022: 2 datasets (1 min + 1 max)

How about the slice expression of

IN([Key Column] ,
LIST(
MAXROW(“Table Name”,“Numeric Field”,[Date]=[_THISROW].[Date]),
MINROW(“Table Name”,“Numeric Field”,[Date]=[_THISROW].[Date])
)
)

[Joseph_Seddik]

Nice one I couldn’t come up with such expression for the slice!

[Asheetbogo]

hello Suvrutt_Gurjar,

thank you so much for your help but i am not at all familiar with string “[_THISROW]”

i search “help.appsheet.com” but i cannot find any help regarding string "[_THISROW] " - what is purbose of string "[_THISROW] "?

[Marc_Dillon]

Asheetbogo:

what is purbose of string "[_THISROW] "?

It’s about expression context. At the start of the expression you begin in the context of the origin record, where you can simply pull values with basic syntax of [column].

But inside of the MAXROW or MINROW (and basically anything based on SELECT), you move into the context of different records, but you still need to reference the values in the origin record. [_THISROW] is a reference to the origin record, then you use the dot-notation “dereference” to pull a particular value from the origin record.

help.appsheet.com

Dereference Expressions

Use a dereference expression to get a column value from the referenced row.

Here’s some more:

When to use [_thisrow] Tips & Tricks 💡

[AppSheet Explained | [_ThisRow]] A very common question, here’s a short video in an attempt to answer this question. (I’m not satisfied with this video, I will do another at some point) Short Answer: When you need to pull data from the table where the formula lives When you’re building a SELECT() formula: [Shared with CloudApp]

[Asheetbogo]

hello Marc_Dillon,

thank you so much - now i can start “deep diving” into expressions …

I am very exited thinking of all the possibilities we have with AppSheet!

[Joseph_Seddik]

I don’t think you can achieve this with a Slice, unless you resort to a higher level of expression complexity.

A better solution would be to create a new table and fill it using an action. The action will be “add a new row to another table” and will act on a single day, every day, adding the desired two rows to the new table.

This action can be triggered either at a certain hour each day, or upon the creation of the first record in a day.

For example, you can configure a bot and set the event as creation of new Table row. The launch condition formula to check if the newly created record is the first record today would be:

NOT( TODAY() = 
  INDEX( 
    SORT(Table[recordDate], TRUE),
    2
  )
)

Then you can launch actions to find the rows with minimum and maximum numbers of yesterday with [recordDate] = TODAY() - 1, and add them to the new table.

[Asheetbogo]

hello KJ Seddik,
fantastic - this is it - i will test it out - thank you so much !!!