topic Re: Average count per day within a certain date range in QlikView

Average count per day within a certain date range

pascaldijkshoor — Mon, 12 Feb 2018 14:23:56 GMT

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

sunny_talwar — Mon, 12 Feb 2018 14:29:45 GMT

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

pascaldijkshoor — Mon, 12 Feb 2018 14:45:36 GMT

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

sunny_talwar — Mon, 12 Feb 2018 14:47:38 GMT

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

stigchel — Mon, 12 Feb 2018 14:48:06 GMT

For a time range something like

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

Depending on your exact requirements

Re: Average count per day within a certain date range

pascaldijkshoor — Mon, 12 Feb 2018 15:03:05 GMT

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

pascaldijkshoor — Mon, 12 Feb 2018 15:03:51 GMT

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

Re: Average count per day within a certain date range

pascaldijkshoor — Mon, 12 Feb 2018 15:16:04 GMT

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

sunny_talwar — Mon, 12 Feb 2018 16:28:19 GMT

May be this

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

Re: Average count per day within a certain date range

pascaldijkshoor — Tue, 13 Feb 2018 07:46:13 GMT

This works perfect, thanks!