• Replies 1 reply
  • Subscribers 962 subscribers
  • Views 90 views
  • Users 0 members are here
Related

Using Conditional Formatting to Highlight Dates/Date Ranges in a Widget?

Allegra Thompson
$organization

We are trying to create a bar graph showing daily Contributed income this calendar year, and then highlight dates when an appeal was active so we can see how successful the appeals were/were not. The date ranges need to be seen in the context of the whole year, so we can't just filter.

I thought maybe I could use conditional color formatting to get this result, but I can't for the life of me figure out how to tell it MAKE THE DISPLAYED DATA FOR THESE DATES RED.

Help?

  • $organization

    Hi Allegra,

    One option would be something like this:

    IF (
     [Max Calendar Month] = 1
     AND [Max Calendar Day Of Month] = 7
     AND [Max Calendar Year] = 2022
     , -1
     , NULL
     )

    Here I'm checking whether, for the given bar of the chart that we're on, the Max Calendar Year is 2022 and the Max Calendar Month is 1 and the Max Calendar Day is 7, which equates to, is the Contribution Date on rows = 2022-01-07. If it is, then return -1 else return 99999999 for comparison with the Total Contribution Amount of the bar. Since the Total Contribution Amount will always be greater than -1, it will reliably change the color of the bar when the date related condition is met. Any comparison of a number against NULL will always be FALSE. Is 0 > NULL is FALSE. Is 0 < NULL is FALSE. Is 0 = NULL is FALSE. So, when the date condition is not met, the Total Contribution Amount > NULL is FALSE and the color is not applied.

    To implement this for multiple dates across the widget, you can click [+ Add Condition], which will duplicate the formula and color of the first condition, and then edit the formula to a different date. Also supported, and better for some users, you can turn that into a CASE statement and put it all in one formula. I've also altered the formula for the date comparison here so that, for me, I can more easily see all the dates in one place, and more easily type the dates as whole numbers in YYYYMMDD format:

    CASE 
    WHEN [Max Calendar Year] * 10000 + [Max Calendar Month] * 100 + [Max Calendar Day Of Month] = 20220107 THEN -1
    WHEN [Max Calendar Year] * 10000 + [Max Calendar Month] * 100 + [Max Calendar Day Of Month] = 20220119 THEN -1
    WHEN [Max Calendar Year] * 10000 + [Max Calendar Month] * 100 + [Max Calendar Day Of Month] = 20220121 THEN -1
    ELSE NULL END

    Note that you can expand the formula editor to more easily edit formulas with a lot of components.