Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
pascaldijkshoor
Contributor

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

Re: Average count per day within a certain date range

May be this

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

9 Replies

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)

pascaldijkshoor
Contributor

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))

stigchel
Honored Contributor

Re: Average count per day within a certain date range

For a time range something like

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

Depending on your exact requirements

pascaldijkshoor
Contributor

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.

pascaldijkshoor
Contributor

Re: Average count per day within a certain date range

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

pascaldijkshoor
Contributor

Re: Average count per day within a certain date range

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?

Re: Average count per day within a certain date range

May be this

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

pascaldijkshoor
Contributor

Re: Average count per day within a certain date range

This works perfect, thanks!

Community Browser