Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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())
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.