Conditional Formatting Formula

Here is the use case, so everyone can see it.

We have 4 "On Sale" phases per season. Donor Early Buy, Package, Single, and "Festival" - which I use for sales once our first concert date starts. These dates vary from year to year slightly, but are roughly the same time of year. I also know that my sales should hit certain percentages of budget in each phase. What I'm building is an Indicator Widget for Sales to Budget with colors for the % of budget hit. That works fine with fixed values. But, at least right now, I have to go update those percentages for each sales phase once the next phase starts.

Side note: I do keep these On Sale Dates in a Local System Table, but don't have a way to bring those dates into the standard date values in Analytics.

So, for example, I know that my Donor Early On Sale should be about 20% of my total budget sales. What I want is an indicator widget that is Red to Black at various percentages of that goal. So, under 85% is Red, 90% is Yellow, 95% is Green, 100% is Blue, 105% is Black. For the early buy, what I need is 20% times those values, or 17%, 18%, 19%, 20%, and 21% of total budget for that period. Packages should add another 20%, so up to 40% of total budget. Same math puts me at 34%, 36%, 38%, 20%, and 21%. Those percentages only come into play once the Package Onsale starts. Then we get about 35% of sales once Single Tickets go on sale, and an additional 25% during the actual Festival. At each time period, I want the indicator widget color to update to how I'm doing against goal.

Obviously, I can go in and manually change the various color criteria amounts after we start a new period. I was just hoping to get a Set It and Forget It method for the season - knowing that a fixed date would still have to be updated when we determine on sale dates for the next festival year.

Since my dashboard can be filtered by Product Line or Specific Performance, the indicator widget can change depending on if I'm look at New York Phil's tracking against budget or even more specifically the Wednesday Opening Night concert. This will let the team know which concerts / product lines are tracking on schedule or falling behind before we get into the festival season, where we are running out of time to impact them.

Here is the use case, so everyone can see it.

We have 4 "On Sale" phases per season. Donor Early Buy, Package, Single, and "Festival" - which I use for sales once our first concert date starts. These dates vary from year to year slightly, but are roughly the same time of year. I also know that my sales should hit certain percentages of budget in each phase. What I'm building is an Indicator Widget for Sales to Budget with colors for the % of budget hit. That works fine with fixed values. But, at least right now, I have to go update those percentages for each sales phase once the next phase starts.

Side note: I do keep these On Sale Dates in a Local System Table, but don't have a way to bring those dates into the standard date values in Analytics.

So, for example, I know that my Donor Early On Sale should be about 20% of my total budget sales. What I want is an indicator widget that is Red to Black at various percentages of that goal. So, under 85% is Red, 90% is Yellow, 95% is Green, 100% is Blue, 105% is Black. For the early buy, what I need is 20% times those values, or 17%, 18%, 19%, 20%, and 21% of total budget for that period. Packages should add another 20%, so up to 40% of total budget. Same math puts me at 34%, 36%, 38%, 20%, and 21%. Those percentages only come into play once the Package Onsale starts. Then we get about 35% of sales once Single Tickets go on sale, and an additional 25% during the actual Festival. At each time period, I want the indicator widget color to update to how I'm doing against goal.

Obviously, I can go in and manually change the various color criteria amounts after we start a new period. I was just hoping to get a Set It and Forget It method for the season - knowing that a fixed date would still have to be updated when we determine on sale dates for the next festival year.

Since my dashboard can be filtered by Product Line or Specific Performance, the indicator widget can change depending on if I'm look at New York Phil's tracking against budget or even more specifically the Wednesday Opening Night concert. This will let the team know which concerts / product lines are tracking on schedule or falling behind before we get into the festival season, where we are running out of time to impact them.

Really helpful...

I think you will need to consider getting these dates in and related to your Analytics data somewhere to leverage them... and you might be half-way there. The Production Season Opening Date might already align with your Festival date, based on your description. And your Performance On-sale Date may align with your Donor Early Buy program, or could be aligned with it. 

Production Season Opening Date: The earliest performance date among all performances in a production season.

Performance On-sale Date: Fields in this dimension reference the Default Sales Dates Start Date for a performance. As each mode of sale can have a different start date, the Default Sales Start Date field can be used to record a single on-sale date to use for reporting in Tessitura Analytics.

Even if those dates cover Donor Early Buy and Festival, there's the 2 others. If these 2 to 4 custom dates were imported as custom fields tied to either the Season or Production Season (or whatever is appropriate based on your needs), then they could be easily used in that DDFF() comparison against NOW() for a set-it-and-forget-it experience.