Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be this if you have a master calendar with all possible dates
Count(DISTINCT Dates)
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.
Does your date is not a date field, but timestamp? May be try this
Count(DISTINCT Floor(Dates))
For a time range something like
=Floor(Max(DateTime))-Floor(Min(DateTime))+1
Depending on your exact requirements
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.
Same problem as i mentioned above, only the count would be 5 i guess.
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?
May be this
Floor(WeekEnd(Max(Date))) - WeekStart(Min(Date)) + 1
This works perfect, thanks!