3

I am trying to create a saved search that shows total orders today, yesterday, this week and this month. I am able to get all but the weekly one using date formulas.

All are Formula(Numberic) fields with summary type Count.

Today: CASE WHEN {trandate} = to_date({today}) THEN {number} END

Yesterday: CASE WHEN {trandate} = to_date(({today} - 1)) THEN {number} END

This Week??

This Month: CASE WHEN {trandate} BETWEEN to_date(TRUNC({today}, 'MONTH'), 'MM/DD/YYYY') AND to_date(LAST_DAY{today}) THEN {number} ELSE 0 END

Any suggestions appreciated!

Marina Pantilus
  • 33
  • 1
  • 3

2 Answers2

1

The way to get this depends on whether you want values for the last 7 days or for this calendar week.

For the last 7 days:

case when  {now} - {trandate} < 7  then {number} else 0 end

or for the current week

case when to_char({now}, 'IW') = to_char({trandate}, 'IW')  then {number} else 0 end

where 'IW' is for the ISO Standard week numbering. You can also use 'WW' for week numbering where week 1 starts on Jan 1, week 2 on Jan 8 etc.

bknights
  • 14,408
  • 2
  • 18
  • 31
0

I'd suggest calculating the day of the week and using that to work back to the start of the week.

I have not tested, but my understanding is that Oracle db functions should work.

to_date({today} - to_char({trandate}, 'D'))

https://livesql.oracle.com/apex/livesql/file/content_GCEY1DN2CN5HZCUQFHVUYQD3G.html

Jose
  • 1
  • 1