Creating a Slice and Filter Rules for when [Date] is more than X Days Ago

[Quinn_Tucker]

Hello!

I’ve been trying to play around with an expression for a slice that will allow me to show all the employees that have not had a visitation record created for them in the last 4 weeks.

I’ve come up with things like the following:

IF(TODAY() > (MAXROW(“Trainee Records”, “Date”)+28),TRUE, FALSE)

MAXROW(“Trainee Records”, “Date”, (IF(TODAY() > ([DATE] + 28)),YES,NO))

None of them have really worked

The main part of my data is the one worksheet (EmployeeRecords) where I have all the visitation records recorded with headings such as ,[Date], [Site], [Employee Name], [Work Overview]. I would have multiple records for the same employee at different dates (i.e. as such RowNumber has become my key).

I would like to be able to have a slice (and then subsequent format rules to make it pretty) that looks at the latest date/visit per Employee in the EmployeeRecords table, then see if that date is more than X days ago (e.g. 4 weeks). If the latest date is more than 4 weeks ago, I want it in the slice. I do not want the slice to show records where the date is more than 4 weeks ago BUT it is not the latest date for any given employee.

I appreciate any advise.

[Marc_Dillon]

1. IF( condition , TRUE , FALSE) is redundant. Just use condition.
2. MAXROW() returns the key value of the matched record, not the column value specified in the 2nd argument.
3. You need to check two conditions in your slice. That the record is the max date for that employee, AND (*hint*hint*), that the date is > X.

Try it out again with these in mind. If you still can’t figure it out I can provide more help.

help.appsheet.com

EXPRESSIONS | AppSheet Help Center

How to use expressions to change the behavior of your apps.

---

Additional note:

Quinn_Tucker:

RowNumber has become my key

_RowNumber as a Key is very much not recommended.

help.appsheet.com

What is a Key?

Learn the purpose of keys and why they’re necessary.

[Quinn_Tucker]

Hi Marc

Thanks for your response.

I tried messing around with this:
AND(MAXROW(“Trainee Records”, “Date”, ([Date] < [_THISROW].[Date])),TODAY() > ([DATE] + 28))

I get this:
Condition AND(ANY(SELECT(Trainee Records[_RowNumber],AND(([Date] < [_THISROW].[Date]), ([Date] = MAX(SELECT(Trainee Records[Date],([Date] < [_THISROW].[Date]))))))), (TODAY() > ([DATE]+28))) has an invalid structure: subexpressions must be Yes/No conditions

As far as I can tell TODAY() > ([DATE] + 28) works in the slice but it will also pull ALL dates and not the most recent (if past X days).

I’m not sure how to combine the different expressions to only get the latest record for each employee if its past x days.

Also quickly in regards to the key, I could always create a UNIQUEID() column and hide it - but I am currently the only app user, so I’m not too worried about using RowNumber as the key for now.

Any further advise appreciated

[Marc_Dillon]

Quinn_Tucker:

MAXROW(“Trainee Records”, “Date”, ([Date] < [_THISROW].[Date]))

Quinn_Tucker:

has an invalid structure: subexpressions must be Yes/No conditions

Again, MAXROW() returns a key value, not a Yes/No value. You need to compare it against something.

Your third argument in MAXROW isn’t helping you. Since you’re wanting the latest date per employee, the third argument should handle that requirement.

[Quinn_Tucker]

Hi Marc

This is what I’ve come up with after looking deeper at other use cases such as this one: Select the last/latest date in a table (rows)

AND(([_THISROW]=MAXROW(“Trainee Records”,“Date”,([_THISROW].[Trainee Name]=[Trainee Name]))),TODAY() > ([DATE] + 28))

I think it is working but not sure I completely understand the format. At this point I’m throwing things in a pot and seeing what happens

Would you be able to help me break it down?

Thanks again.

[Marc_Dillon · Accepted solution]

Nice work. That looks correct to me.

Quinn_Tucker:

Would you be able to help me break it down?

Sure, I can try.

So you have 2 conditions that each record in your Table must match to be in your slice. Those are your 2 arguments in the AND().

I’m sure you understand the [Date] comparison.

So for the other argument, you’re wanting to match if the record is the latest record for the same Trainee as in the current record (the condition is evaluated iteratively across all records, so the “current” record changes every time). For every record of the same Trainee, the MAXROW() expression is going to return the same key value, but you only want it to match a single record. So with just this single condition, you’re getting only the latest record per each Trainee.

Another expression you could have used for this condition, which may make more sense to you, is this:

[Date] = 
MAX( SELECT(
  Trainee Record[Date] ,
  [Trainee Name] = [_THISROW].[Trainee Name]
) )

[Quinn_Tucker]

Marc_Dillon:

[Date] = 
MAX( SELECT(
  Trainee Record[Date] ,
  [Trainee Name] = [_THISROW].[Trainee Name]
) )

Hi Marc

Yes that way does seem much more easier to follow than the one I pasted above.

So essentially the full thing would look like

AND(([Date] = MAX( SELECT(Trainee Record[Date] ,[Trainee Name] = [_THISROW].[Trainee Name]) ), TODAY() > ([DATE] + 28))

Apologies for not getting back to this sooner, I have about four different posts going for the one application - trying to take it all onboard.

Thank you again for your continued assistance!