counting numbers by day of week in a calculated field
Posted on 2014-09-26
Hi there, I am running into a problem with calculated fields in regard to day of the week. So, I ran data which gave me counts of something arbitrary, let's call them widget purchases. So I was trying to find out how many were purchased, on avg, by hour. So this was an easy calculation, because there were 91 days in Q2, 2014, I just took the total widgets sold by hour and divided it by 91. That was the easy part.
The next step was to do the same comparison using day of the week. In this case, I had the day of week in my rows, and then a calculated field which told me the avg. amount sold by day of the week. I got lucky because there were 91 days in the quarters and amazingly enough, there were 13 of each day of the week. So my calculation was just total told on all Mondays, divided by 13 because there were 13 Mondays.
My problem is what happens next quarter when there aren't an identical number of days for each day of week. So the pivot would look something like this.
Total Sold Avg. Daily Sold (Calculated field)
Mon 100 = total sold for Monday/13 because there are 13 Mondays in Q2, 2014.
Tue 102 = total sold for Tuesday/13
Wed 124 " " "
Thu 118 " " "
Fri 116 " " "
Sat 94 " " "
Sun 82 " " "
So, how can I create a calculated field based on the # of those days in that time period. Any thoughts?