Return both the max and min values in a slice

AppSheet AppSheet Q&A
1

Hello,

I am trying to return both the max and min [Hcap] values per [Golfer ID] per month. I can do only or the other.

HcapID GolferID Hcap Date
1 1 16 03/11/2019
2 2 12 03/11/2019
3 1 18 04/11/2019
4 2 23 05/11/2019
5 2 17 06/11/2019

Ideally I would like to UNION both in a Slice

[_THISROW] = MAXROW(“HCAPGUI”, “_ROWNUMBER”, AND( [_THISROW].[GolferID] = [GolferID], EOMONTH([Date],0)=EOMONTH([_THISROW].[Date],0) ) ) AND [_THISROW] = MINROW(“HCAPGUI”, “_ROWNUMBER”, AND( [_THISROW].[GolferID] = [GolferID], EOMONTH([Date],0)=EOMONTH([_THISROW].[Date],0) ) )
UNION
[_THISROW] = MINROW(“HCAPGUI”, “_ROWNUMBER”, AND( [_THISROW].[GolferID] = [GolferID], EOMONTH([Date],0)=EOMONTH([_THISROW].[Date],0) ) ) AND [_THISROW] = MINROW(“HCAPGUI”, “_ROWNUMBER”, AND( [_THISROW].[GolferID] = [GolferID], EOMONTH([Date],0)=EOMONTH([_THISROW].[Date],0) ) )

Thanks

Pat

2 
OR(
  ISBLANK(
    FILTER(
      "HCAPGUI",
      AND(
        ([_THISROW-1].[Hcap] < [Hcap]),
        ([_THISROW-1].[Golfer] = [Golfer]),
        (EOMONTH([_THISROW-1].[Date], 0) = EOMONTH([Date], 0))
      )
    )
  ),
  ISBLANK(
    FILTER(
      "HCAPGUI",
      AND(
        ([_THISROW-1].[Hcap] > [Hcap]),
        ([_THISROW-1].[Golfer] = [Golfer]),
        (EOMONTH([_THISROW-1].[Date], 0) = EOMONTH([Date], 0))
      )
    )
  )
)
1 Like
3

Genius, thanks for your help!

1 Like