Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pascaldijkshoor
Creator
Creator

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

1 Solution

Accepted Solutions
sunny_talwar

May be this

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

View solution in original post

9 Replies
sunny_talwar

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

Count(DISTINCT Dates)

pascaldijkshoor
Creator
Creator
Author

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
Partner - Master
Partner - Master

For a time range something like

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

Depending on your exact requirements

pascaldijkshoor
Creator
Creator
Author

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
Creator
Creator
Author

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

pascaldijkshoor
Creator
Creator
Author

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

May be this

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

pascaldijkshoor
Creator
Creator
Author

This works perfect, thanks!