Solved: Creating a Slice and Filter Rules for when [Date] ... - Page 2

Quinn_Tucker · 08-02-2021 08:16 PM

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

Nice work. That looks correct to me.

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

View solution in original post

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