Counting Items Entered on each Day of the Week

[product26]

I need to count items entered based on the day of the week.

Basically we have orders.orderid. each orderid entry has a corresponding orders.dtentered. I need to count all the dtentered for each day of the week.

hoping to get a count of # entered on Mon, # entered on Tues. # entered on Wed. etc.

What date function would I use, and how would I apply this to a formula or a running total?

 

[lbass]

If you don't have row inflation, you should be able to create formulas like this:

//{@Mon}:
if dayofweek({table.date}) = 2 then 1 //Sun = 1, Mon = 2, etc.

Then right click on this formula in the detail section and insert a sum (NOT a count) on it.

-LB

 

[product26]

excellent! I think I am on the right track now.

Thank you!

 

[product26]

Something more to ask.

I have 1 day of the week that overlaps. Our product is delivered on Thursdays, and that is the day that the day that each entry is centered around each week.

What we have is multiple dates registering as Wednesday each week. So for each delivery day (thursday) there are 2 wednesdays that entries can be made. I need to separate them to show which ones were done a week ahead of time and which were done at the last minute.

What I have done is try to create two formulas and create a summary of each. The two formulas are supposed to narrow it down to everything that was entered on a Wednesday that is the day before the start date (always thursday) and everything that was entered on wednesday that is anything but the day before the start date.

However, I am not getting the correct results. Here are the formulas.

if {orders.dtentered}=DateAdd('d', -1,{orders.startdate})
and dayofweek({orders.dtentered}) = 4 then 1

 

[product26]

and

if {orders.dtentered}<>DateAdd('d', -1,{orders.startdate})
and dayofweek({orders.dtentered}) = 4 then 1

I am thinking that 'dateadd' must not be correct, because it is not returning the expected results.

 

[product26]

perhaps

if {orders.dtentered}=DateSerial(Year({?StartDate}), Month({?Startdate}), 1 - 1)
and dayofweek({orders.dtentered}) = 4 then 1

 

[product26]

Only problem with what I last posted is that on the weeks that span over the end of one month and the first of the next, the values are reversed (The number of items entered on wednesdays are swapped).

for example: 12 items were entered on Wednesday October 24th that are to be delivered on Thursday November 1st. These Items were entered a week in advance. 0 items were entered on Wednesday October 31st, and yet it shows that 0 were entered on the 24th and 12 were entered on the 31st.

Other weeks that do not span the first/last of months do show up properly.

 

[lbass]

Your original formulas look right to me:

if {orders.dtentered}= DateAdd('d', -1,{orders.startdate})
and dayofweek({orders.dtentered}) = 4 then 1

if {orders.dtentered}<>DateAdd('d', -1,{orders.startdate})
and dayofweek({orders.dtentered}) = 4 then 1

You could also use:

//{@wedssameweek}:
if datepart("ww",{orders.startdate}) =
datepart("ww",{orders.dtentered}) and
dayofweek({Orders.dtentered}) = 4 then 1

//{@wedsdiffweek}:
if datepart("ww",{orders.startdate}) <>
datepart("ww",{orders.dtentered}) and
dayofweek({Orders.dtentered}) = 4 then 1

-LB