2 Replies Latest reply on Aug 21, 2013 8:29 AM by Tom Jones

# Determining the MAX value for each value in a given dimension

I’m trying to calculate the maximum YTD daily case volume at each facility.  My data looks like:

 Facility Date Caseid A 1/1/2013 1 A 1/1/2013 2 A 1/1/2013 3 B 1/1/2013 4 C 1/1/2013 5 A 1/2/2013 6 B 1/2/2013 7 B 1/2/2013 8 C 1/2/2013 9 A 1/3/2013 10 B 1/3/2013 11 C 1/3/2013 12 C 1/3/2013 13

I’d like to create a worksheet showing the max number of cases, along with the date (when the maximum cases occurred)  for each facility:

 Facility Date Max Case Count A 1/1/2013 3 B 1/2/2013 2 C 1/3/2013 2

I’ve tried different approaches using Number of Records and COUNTD(Caseid), without success.  I’ve tried using a calculated field as a True/False filter like COUNTD(CaseID)=WINDOW_MAX(COUNTD(CaseID)) , but cannot get it to show the maximum value for each facility.

Any recommendations?

• ###### 1. Re: Determining the MAX value for each value in a given dimension

Tom,

Here is a solution:

1) Create view showing countd(caseid) by facility, by date.

2) Create table calculation that gets the first value of the date in the partition.

Date with the most cases = LOOKUP(MAX([Date]),FIRST())

This has to be an advanced table calculation addressing Facility/Date sorted by countd(caseid) descending, restarting every Facility:

"Results are computed along Month, Day, Year of Date (sorted descending by Distinct count of Caseid) for each Facility."

3) Then you have to filter and keep the row that has the highest number of cases.

Filter = first()==0

Use the same advanced table calculation settings.

Not sure if there is a simpler way, but this works.

Pedro

• ###### 2. Re: Determining the MAX value for each value in a given dimension

Pedro,

Thanks -- this solution provided me with some ideas to solve some of my other 'challenges' as well!

Thanks for the guidance!

Tom J.