topic Finding column value associated with minimum value in another column in App Development

Finding column value associated with minimum value in another column

stascher — Thu, 19 Apr 2018 18:18:47 GMT

Hello,

This should be fairly easy. Given the following table:

Subject	Visit Date	Visit Reason
Arty	1/1/2001	Alpha
Arty	2/2/2002	Beta
Arty	3/3/2003	Delta
Betty	1/15/2015
Betty	2/16/2016	Biff

I want to create a Table Listing of Subjects with the date of their first visit and, if provided, the visit reason concatenated to the date. Ex:

Header 1	Header 2
Arty	1/1/2001 - Alpha
Betty	1/15/2015

I've got my Table Listing defined with Subject as Dimension #1, and I can define min([Visit Date]) to get the first visit, but I'm not sure of the rest of the expression I need to get the Visit Reason associated with that date.

Thanks for any help.

Steve

Re: Finding column value associated with minimum value in another column

sunny_talwar — Thu, 19 Apr 2018 19:40:25 GMT

Try this

Dimension

Subject

Expression

Date(Max([Visit Date])) & ' - ' & FirstSortedValue([Visit Reason], [Visit Date])

Re: Finding column value associated with minimum value in another column

stascher — Thu, 19 Apr 2018 20:15:05 GMT

Once again, Sunny to the rescue!

I'd never heard of the FirstSortedValue function before, so I learned something new today.

Thank you very much!