8 Replies Latest reply on Jun 11, 2014 10:23 AM by Matt Lutton

    Nested Aggregation

    Taag Ebert

      Hi.  I think the term for what I'm trying to accomplish here is 'Nested Aggregation', and probably I need to do the 1st-order rollup in the input data source itself.  But wondering if anybody has an idea for how it might be done with the data source as its defined.


      Basically I have sampling of transactions with timestamps.  From that I want to roll-up the number of records by hour-of-day.  And then I want to calculate aggregates such as Max, Average, etc... (by hour of day) from those totals.


      The first worksheet shows a roll-up of transaction counts by Continuous timestamp hours.  And in the 2nd worksheet I'm trying to take a Maximum of transaction counts by Discrete timestamp hours.  (i.e.  Would like to see a value of 55 for the 4 am hour, 77 for the 5am hour, etc... )   But of course I just get Max row count values = 1, as it's being calculated at a record level.


      Thanks for any suggestions.

        • 1. Re: Nested Aggregation
          Matt Lutton

          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.

          • 2. Re: Nested Aggregation
            Taag Ebert

            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!

            • 3. Re: Nested Aggregation
              Matt Lutton

              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!


              • 4. Re: Nested Aggregation
                Taag Ebert

                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.

                • 5. Re: Nested Aggregation
                  Matt Lutton

                  Weird.  I had the correct answer for sure before I posted.  I'll get it back... give me a minute.    

                  • 6. Re: Nested Aggregation
                    Matt Lutton

                    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.

                    • 7. Re: Nested Aggregation
                      Taag Ebert

                      Great, I'll do some reading on Table Calculations and maybe get back to you with specific questions.


                      Thanks again for looking at this!

                      • 8. Re: Nested Aggregation
                        Matt Lutton

                        No worries, be patient. It takes time and practice. Myself, and plenty of others, are here if you need help with specific aspects.  Cheers