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

# Table Calc Using Max

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

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

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

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.