Need help debugging saved search formula

Hello all,

I am working a a saved search formula to get transactions per hour per day for January 2021 to start. Columns would be location (one location to test), day of the week, total # of transactions, and transactions per hour for that day.

Results are set as follow:

Location (grouped)

Formula(text) TO_CHAR({trandate},’DAY’) (grouped)

Document # (count)

Formula(numeric) (sum)

CASE
WHEN (TO_CHAR({trandate},’DAY’)) = ‘SUNDAY’ THEN 1/40
WHEN (TO_CHAR({trandate},’DAY’)) = ‘MONDAY’ THEN 1/44
WHEN (TO_CHAR({trandate},’DAY’)) = ‘TUESDAY’ THEN 1/44
WHEN (TO_CHAR({trandate},’DAY’)) = ‘WEDNESDAY’ THEN 1/44
WHEN (TO_CHAR({trandate},’DAY’)) = ‘THURSDAY’ THEN 1/44
WHEN (TO_CHAR({trandate},’DAY’)) = ‘FRIDAY’ THEN 1/55
WHEN (TO_CHAR({trandate},’DAY’)) = ‘SATURDAY’ THEN 1/55
ELSE 0
END

The denominator of the fractions represent the total operational hours for jan 2021. The issue is WEDNESDAY is the only day that returns anything above 0 and I can’t figure out why. Any help is much appreciated!

Rookie Asked on May 7, 2021 in Saved Searches.
Add Comment
1 Answer(s)

Nevermind, I used a different parameter ‘D’ and associated numeric value ‘1’, ‘2’, etc. and got the search to return the correct values. Strange…

Rookie Answered on May 7, 2021.
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