Get the value based on Min Date

[Badzreyes00]

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!

[rubenmarin]

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'
))

[Badzreyes00]

Hi Ruben, 

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

[Badzreyes00]

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

[rubenmarin · Accepted solution]

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.])

[Badzreyes00]

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!

[rubenmarin]

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