OK I modelled this to work with QX (see 'ForLouisZA.xls' in the files
section), however, can't you just take the total per time interval and
divide by the number of days in the total database? In the model, I've
shown both ways of doing it. The model has 30 min intervals from 6am
to 8pm, 16-20 April (5 days).
Note I have purposely completely deleted any queues from 12:00 to
12:30 on the 16 April to make a point: the QX calculation
automatically notes that there are only 4 batches of 12:00 to 12:30
data and thus comes to a higher average (over 4), while the 'extended
pivot table' method averages it over the 5 days and comes to a lower
average.
It seems to me that the pivot table method is commercially more
relevant: say you'd only had data between 12:00 and 12:30 ONE DAY all
year (the rest of the days the queue users were always having lunch or
something), I would think you would want your summary to indicate
very, very low daily activity between 12:00 and 12:30, but an average
of the actual datapoints in this time interval would be the total of
that particular day.
As for the qx method, I thank you for your praise in the other thread
and hope you've installed the function, as I didn't include it in the
file noted above. The formula is provided twice in a quotient in the
format:
qx([DB],"sum",[TimeIntervalCol],TimeIntervalTarget])/
qx([DB],qmask("^,^",[Date & Time Interval cols]),"countu",
[TimeIntervalCol],TimeIntervalTarget])
The Numerator: qx([DB],"sum",[TimeIntervalCol],TimeIntervalTarget]):
Simply sums up the 'count' data matching the Time Interval Target
(regardless of queue or date). This is also what the pivot table is
doing (ie it's not going straight for the average as you attempted).
The denominator: qx([DB],qmask("^,^",[Date & Time Interval
cols]),"countu",[TimeIntervalCol],TimeIntervalTarget]) is a bit more
involved, as it uses the 'QMask' function:
It counts how many unique occurrences of a given date and time are in
the database. In the example, this is '5' for all time frames except
12:00-12:30, in which case it is '4'.As the data and time are in two
different columns, the QMask column was needed to make a 'virtual
column' consisting of the date and time in one string divided by a
comma ("^,^"- that's not a smiley lol; look at the QMask section in
the QX instructions). QX then takes the QMask result and counts the
unique occurrences of each date & time (ie several occurrences of the
same date and time count as one).
The QX section of the model refers to the times in the pivot table
only for convenience: any list of time intervals (ie not necessarilly
from a pivot table) would suffice as it merely provides the target in
the qx functions.
= Doug
On Apr 15, 1:34 pm, LouisZA <lpien...@gmail.com> wrote:
> Hi,
>
> I need some help with a pivot table.
>
> I have a dataset with four columns:
>
> Date, TimeInterval, QueueName, count
>
> Each row is then the count of items for that date for an time interval
> per Queue. In this case, the time interval is every half hour.
>
> So the data is basically the number of items per half hour for the
> last year for each queue.
>
> When I do a pivot table with TimeInterval as row and average of count
> in data. I essentially get the average per que for each time
> interval.
>
> How do I get the average of the sum of all queues per time interval?
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
