topic Re: Date set comparison in QlikView

Date set comparison

Mon, 14 Nov 2011 19:04:41 GMT

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!

Date set comparison

Mon, 14 Nov 2011 19:18:52 GMT

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

Re: Date set comparison

johnw — Tue, 15 Nov 2011 01:00:52 GMT

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.