topic Re: Get the value based on Min Date in App Development

Get the value based on Min Date

Badzreyes00 — Sat, 16 Nov 2024 18:45:08 GMT

Hi All,

Hope you are all doing well, I hope you can help me with my dilemma below,

my desired output is the "REMARKS" column. I am having problem doing this in IF condition since I need also to check if there is another PLACE value.

Person No.	Place	Date	Remarks
1001	Home	8/20/2018	Home first, then work
1001	Work	1/10/2019	Home first, then work
1001	Work	1/12/2019	Home first, then work
1002	Home	8/10/2019	Home Only
1003	Work	9/20/2019	Work Only
1004	Work	9/20/2019	Work first, then Home
1004	Home	9/21/2019	Work first, then Home
1004	Work	9/23/2019	Work first, then Home
1005	Home	9/25/2019	Work first, then Home
1005	Work	9/23/2019	Work first, then Home
1005	Work	9/24/2019	Work first, then Home
1006	Home	10/10/2019	Home Only

The syntax that I am using is

if
(
If(Aggr(NODISTINCT Min(Date), Person_No) = Date, Place)='Home','Home Only',
if
(
If(Aggr(NODISTINCT Min(Date), Person_No) = Date, Place)='Work','Work Only''
))

I cannot figure out how can I check the 2nd value for for Place column so that I can input the condition for "Work first, then Home" or "Home first, then Home"

I would really appreciate the help, thank you so much in advance!

Re: Get the value based on Min Date

rubenmarin — Wed, 22 Apr 2020 14:04:31 GMT

Hi, can you check if this expression works?:

If(Count(DISTINCT TOTAL <[Person No.]> Place)=1 ,If(Place='Home', 'Only home', 'Only Work') ,If(FirstSortedValue(TOTAL <[Person No.]> Place, Date)='Home', 'First Home then Work', 'First Work then Home' ))

Re: Get the value based on Min Date

Badzreyes00 — Thu, 23 Apr 2020 01:06:32 GMT

Hi Ruben,

Thank you so much for your response. It is "OK" in script editor but after applying I encountered an "Invalid Dimension" Error :(((

Re: Get the value based on Min Date

Badzreyes00 — Thu, 23 Apr 2020 01:15:54 GMT

I tried changing those fields with < > into , or adding {}. but it only giving me the Else value to all 😞

Re: Get the value based on Min Date

rubenmarin — Thu, 23 Apr 2020 06:38:42 GMT

Hi it's intended to work as expression. To make it a dimension you need to enclose the expression in an aggr():

Aggr(Expression, [Person No.])

Aggr(If(Count(DISTINCT TOTAL <[Person No.]> Place)=1 ,If(Place='Home', 'Only home', 'Only Work') ,If(FirstSortedValue(TOTAL <[Person No.]> Place, Date)='Home', 'First Home then Work', 'First Work then Home' )), [Person No.])

Re: Get the value based on Min Date

Badzreyes00 — Thu, 23 Apr 2020 13:43:56 GMT

Hi Ruben,

It works on the first set of expression(Home only and work only) but does not work for home first then work and work first then home. The output it like below:

Place	Person_no	Date	Remark
Home	100000101	05/23/2019	First Work then Home
Work	100000101	05/30/2019	First Work then Home
Work	100000101	06/07/2019	First Work then Home
Work	100000101	06/13/2019	First Work then Home

I tried tweaking if but failed, I appreciate your help hope you can help me with this one. Thank you!

Re: Get the value based on Min Date

rubenmarin — Thu, 23 Apr 2020 13:54:33 GMT

I don't know, seems working for me. Sample attached.