Tried The Hour But No Change, I Have New Idea Possibly?
Rate this:
(3/5 from 1 vote)
Hi & thanks for responding. On the second suggestion I added the Hour column by itself, and as I already had a count column with 1's in it from a prior attempt I then refreshed and modified the pivot to use the hour field on the vertical axis and kept the DayOfWeek field on the horizontal and used the average against the Count field as the value, but still only returned 1's in the pivot as before, so it didn't seem to matter in using the Hour field separately or the group by Hour against the OpenDateTime field in this case.
I am not sure how to go about your other suggeston of creating a calculation table using the body and totals of the pivot table.
In trying to figure out why the pivot is showing a low average number of 1's, I changed the pivot to remove the hour side of it simply to get an average for tickets coming in on the days of the week only. I then stuck an abnormally large test value in one of the Count data row fields in place of a 1 and then I DID see the corresponding average value in the Pivot change. What I realized then was the pivot average calculation was simply adding up all the counts for the Mondays and then dividing it by the number of rows in the data that fit that criteria, and since they count values were all 1's the sum is always the same as the number of rows so the resulting average would ALWAYS be 1. This does me no good obviously. So that got me to thinking there needs to be another type of grouping in the raw data so that the count column values are not always 1, like grouping by week number maybe and getting a total count of tickets for each day of the week for all the weeks of the year first, and then calculate the average across those groupings?
