You should be able to get what you want using table calculations, if I understand your goal properly.
Where are you getting those Maximum Counts from? (55, 77, etc.?) I'm getting something like 76 for 4am and 106 for 5am, etc. I want to make sure I understand the goal before I post anything. Cheers.
I've attached a slightly revised version above (TransCounts2.twbx) in which I added a filter on the hour-of-day for the 1st worksheet.
The 1st worksheet (ContinuousHrlyTransCnts) has the sum of transactions counts for each hour-of-day across multiple days (May 19 - May 27).
The goal on the 2nd worksheet (DiscreteHrlyTransCnts) is to display, for each hour-of-day, the maximum transaction count that occurred across the entire date range. So, using the new filter on the 1st worksheet, it's easy to see that the maximum count for the 4 am hour was 55, which occurred on May 22 & 27th. And the max count for the 5 am hour was 77, which occurred on May 23rd. Those are the values I'd like to have represented on that 2nd sheet.
Thanks for taking the time to look at this!
OK, here you go:
What I did:
1) Created a calculated field for the Maximum Count per hour:
IF FIRST()==0 THEN WINDOW_MAX(COUNT([Slf_Svc_Dvc_Id])) END
The IF FIRST()==0 part is an optimization, without it, we get the same value multiple times for each bar (note that I've placed a discrete version of the Exact Date field on the Detail shelf in order to get this to evaluate properly).
The discrete exact date was added to the detail shelf, and this seemed to be the magic bit. Then, the compute using settings for the Table Calculation just needed to be set to: Addressing on Hour, then the Exact Date field, restarting every Hour.
I hope this helps--let me know if you have any questions!
Hmmmm. There's a lot of stuff that's new in that for me to digest. Not familiar with any of those functions.
But before I pick into them, the values being produced still look different than what I'm hoping to get. (see attached revision)
Should be getting 55 for the 4 am hour, and 77 for the 5 am hour. Not 78 & 106.
Weird. I had the correct answer for sure before I posted. I'll get it back... give me a minute.
Looks like I uploaded the wrong workbook file--the calculations I described will work if you try them (see the screenshot), but I'll set it up again. It'll be difficult to understand if you've not used Table Calcs before--there isn't much I can do except suggest that you study up and practice learning them at your own pace. Cheers.
Here's a great resource to start with:
I'm happy to try and describe/explain or walk through anything that is unclear.
Great, I'll do some reading on Table Calculations and maybe get back to you with specific questions.
Thanks again for looking at this!
No worries, be patient. It takes time and practice. Myself, and plenty of others, are here if you need help with specific aspects. Cheers