Average count per day within a certain date range

[pascaldijkshoor]

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

[sunny_talwar]

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

Count(DISTINCT Dates)

[pascaldijkshoor]

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.

[sunny_talwar]

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

Count(DISTINCT Floor(Dates))

[stigchel]

For a time range something like

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

Depending on your exact requirements

[pascaldijkshoor]

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.

[pascaldijkshoor]

Same problem as i mentioned above, only the count would be 5 i guess.

[pascaldijkshoor]

As an addition:

The data consists of shipments for all dates within the date range. However for the dimension I want to use Load stations. Not every load station has shipments for all dates within the date range. Does it help to use a master calendar in this case?

[sunny_talwar · Accepted solution]

May be this

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

[pascaldijkshoor]

This works perfect, thanks!