Saved Search Formula Not Working

Hello!  I’m working on cleaning up and updating a saved search for resource forecasting that a predecessor set up.  It is set up to look at an event start and end time for an employee and if they have an event at that time to show “NA”.  However for the 8:00-8:15AM (converted to decimal) time bucket and a couple other time buckets, the formula doesn’t work, (shows “A” when should be “NA”) but works for the majority of them.   I’ve checked the event set up and nothing sticks out as being off as well as adjusting the number of decimal places.

CASE
WHEN {starttime}- TRUNC({starttime})=0.333333333333333 OR (0.333333333333333>{starttime}- TRUNC({starttime}) AND 0.333333333333333<{endtime}-TRUNC({endtime}))
THEN ‘NA’
ELSE ‘A’
END

Thanks for your help in advance!

Rookie Asked on January 27, 2022 in Saved Searches.
Add Comment
2 Answer(s)
Best answer

Hi,

I get the feeling there may be an easier overall solution but working with what you have, I’d convert everything to minutes in order to get rid of those decimals:

CASE WHEN 24*60*({starttime}-TRUNC({starttime}))=480 OR (480>24*60*({starttime}-TRUNC({starttime})) AND 480<24*60*({endtime}-TRUNC({endtime}))) THEN 'NA' ELSE 'A' END

If that doesn’t work then theres’ something more fundamentally wrong with the logic.

Let me know if you need any further help.

Thanks,

Chris

Intermediate Answered on January 28, 2022.
Add Comment

Hi Chris,

I agree, there is probably a much easier solution but not looking to recreate the wheel on this one with my limited knowledge of SQL/javascript!  Your solution fixed the issue for the 8AM time bucket so going to update all of the formulas to use minutes vs decimals and that should fix the issue I’m running into!

Thank you so much for your help with this!

Rookie Answered on January 28, 2022.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.
  • This site made possible by our sponsors:   Tipalti   Celigo   Limebox   Become a Sponsor   Become a Sponsor