9 Replies Latest reply: Feb 13, 2018 2:46 AM by Pascal Dijkshoorn

# Average count per day within a certain date range

Hello,

I want to calculate the average count of shipments per day within a filtered time range.

Is there a formula that counts the amount of days within your date filter? So I can create the formula: count (shipment_number) / # of days in filter?

Or are there any other possibilities to achieve this?

Regards,

Pascal

• ###### Re: Average count per day within a certain date range

May be this if you have a master calendar with all possible dates

Count(DISTINCT Dates)

• ###### Re: Average count per day within a certain date range

I have tried this, however when I selected one week in the filter, I got results varying from 1 to 9, while all should be 7.

• ###### Re: Average count per day within a certain date range

Does your date is not a date field, but timestamp? May be try this

Count(DISTINCT Floor(Dates))

• ###### Re: Average count per day within a certain date range

The problem with this is that if in a week for a certain dimension it has:

1 shipment on monday

1 shipment on tuesday

1 shipment on friday

The count for the week is 3, so the average will be 3 as well, while the average should be 3/7.

• ###### Re: Average count per day within a certain date range

May be this

Floor(WeekEnd(Max(Date))) - WeekStart(Min(Date)) + 1

• ###### Re: Average count per day within a certain date range

This works perfect, thanks!

• ###### Re: Average count per day within a certain date range

For a time range something like

=Floor(Max(DateTime))-Floor(Min(DateTime))+1