I currently have a sample data set that contains request ID's start and done dates. My objective is to create a dashboard that will display the number of Active request IDs at an hourly basis based on a parameter Start Date.
Within the attached workbook...since the request IDs within my data source do not have every hour of the day included, I also added a data source that contains the date and hour of the day. To accomplish this via "blending", the date and hour data source will be the primary. Note that Requests (Done) and Request (Start) are identical data sources but duplicated in order to do the blending relationships.
In the attached workbook, I have been successful to calculate new requests, done requests, and active requests (based on the rolling difference). As you can see, the Hourly Started/Done and Active Requests worksheet returns my results based on the defined Start Date of 3/5/2014. The Hourly Active Requests dashboard as well provides insight to trend of these Active requests and is working as I would like.
The worksheet called "Weekday Hour Active Totals" is what I am currently having issues with. I am trying to to group these calculated results by Weekday and then Hour of the day with the goal to see what hour of the day throughout the week do we have the most "Active" Request IDs.
As an example, for the Weekday of Sunday at the 5am hour, currently the Active Total is -2. The results I would like to have would be 75...because in the Hourly Started/Done and Active Requests worksheets, the following are the two Sundays at 5AM.
3/9/14 5 AM (Sun)
3/17/14 5 AM (Sun)
I know I will probably need a different way to compute my table calculations, but not sure next steps.