Whats the best way to classify events as being "Normal" or "Exception" type of events based on time ranges when i have various different exception windows timings?

[magenta]

I have historical events that i'm looking to classify as having occurred during an exception period or not.
The challenge is that there are 12+ categories of events and each type of event has 4 slightly different "Exception Periods" each year. For some of the categories, the exceptional time period can be defined based on # of work days before and after the end of the quarter.

Is there a good way of doing this outside of writing many many IF statements?

[martin_mueller · Accepted solution]

You could write a time-based lookup with three columns: _time, category, period

For each category, you'd put in a row for each transition between normal and exception, along with the timestamp for the transition and the class that started then. The time-based lookup logic should retrieve the youngest row for each event's category that's still before the event's timestamp.

[magenta]

Thanks i didn't realize lookups could be time based so i think this should work!