3 Replies Latest reply on Jul 27, 2012 11:08 AM by John Sobczak

    Table Calc Using Max

    John Sobczak

      I am struggling with how to do a table calc on the following scenario:

       

      I'm basically plotting 2 fields:  # of Tickets (vertical row measure) vs Month (horizontal column dimension). However I am further breaking up the measure marks by another measure - which is # of tasks within that ticket.  So for example in July there were 10 tickets total, 3 tickets with 1 task, 4 tickets with 2 tasks and 3 tickets with 5 tasks.

       

      I'm only want to show the highest (max) count of tickets in the month by number of tasks.  So in above example this would be 4 tickets (highest count) and those tickets had 2 tasks.  This is the "mode" in the distribution - the number that appears most often.

        • 1. Re: Table Calc Using Max
          Tracy Rodgers

          Hi John,

           

          Is tasks a measure as well?

           

          A calculated field similar to the following might help:

           

          window_max(sum([Number of Tickets]))

           

          Then, right click on this and select Compute Using-->Table (Across)

           

          -Tracy

          • 2. Re: Table Calc Using Max
            John Sobczak

            Tracy,

             

            Yes Tasks is a measure as well, but it could also be a dimension.  This is where my trouble lies how to get BOTH metrics labeled and just have ONE Mark per column (month) .   The Mark is plotted  based on the count and month, but I also need the number of tasks or type of ticket for that Mark. 

             

            Key to above logic is that the Mark represents the type of ticket (ie, # of tasks in ticket) with the highest frequency in that month.

            • 3. Re: Table Calc Using Max
              John Sobczak

              Tracy,

               

              Just one more piece of information.  I can get the above without a table calculation, but showing all the marks for all the ticket types.  I just want to show the top most mark which represents the highest count.  I have tried using variations of window_max in a custom table calc and just have not found the right formula to get the desired result.