Date set comparison

[#1]

I have a field called DaysWorked containing a list of about 400 dates.

I also have a table containing the list of national holidays (HolidayDate), containing about 8 dates.

I wish display the list of DaysWorked, which will be bright red if the date lands on a national holiday. Essentially, if DaysWorked is in HolidayDate, then true. I am having difficulty getting the hang of set arithmetic.

As an extra challenge, Id like DaysWorked to be a duller shade of red if it is +/- 1 day either side of a national holiday.

Im new to QV and BI generally. I would appreciate any help!

[#2]

If the table of holidays is linked with the table containing days worked, then this should work:

if(date(DaysWorked)=date(HolidayDate), Red(), if(date(DaysWorked-1)=date(HolidayDate) or date(DaysWorked+1)=date(HolidayDate),Lightred())

[johnw · Accepted solution]

Use this as the background color expression.  Change the two colors as desired. See attached.

pick(1+min(aggr(fabs(Date-Holiday),Holiday,Date)),lightred(),rgb(255,150,150))

It would probably help performance to flag these dates in the script.  It sounded like you wanted an expression, though.