Way to structure long formula (like a CASE statement)?
We are trying to track the responsiveness of various groups to incoming leads. The stages in the process are: the lead is received by Admin; Admin sends it to Sales to decide which sales person to assign it to; Sales responds to Admin, and Admin creates a new opportunity for that lead. The problem is the leads come in all through the day and night, and they are timestamped in our system with the time they are received (if the prospect is in Europe, for example, it's not unusual to get a timestamp of 3 AM EST). The nominal start time of our Admin staff is 9 am, but there are days that they are in earlier, and begin processing leads at 7:30 AM.
On the other hand, sometimes the leads come in after 5 pm, by which time the Sales staff is usually gone. They usually respond the next morning, but if I simply subtract the incoming lead time from the response time, it will be 16 hours or more, which skews the results. (There is no "networkinghours" function, of course).
Then there are leads that come in on Friday night, or Saturday or Sunday. Sometimes the lead is referred to Sales on Friday after 5 pm, and they don't respond until Monday morning, resulting in an apparent response time of 80+ hours.
I've constructed a very lengthy nested IF statement (5 nested formula), which is hard to debug (since you can't even see it all on the screen at one time). I'd much prefer to build something more structured, and thus more maintainable. It cries out for a "CASE" statement.
Does QV have anything in the chart expressions that might help me out? I couldn't find anything, but I'm finding the documentation is incomplete.
