5 Replies Latest reply on Mar 10, 2017 9:03 AM by Joe Oppelt

# Calculated field for tooltip display - MAX

Since Zip Codes can have multiple Cities, I need to find the City with the highest Count of Patients within a Zip Code to be displayed on my tooltip.  Anyone know a quick calculation to accomplish this?

Thanks

• ###### 1. Re: Calculated field for tooltip display - MAX

{ FIXED [zip code] : MAX([Patients]) }

Unless you have multiple rows per city...

• ###### 2. Re: Calculated field for tooltip display - MAX

Thanks Joe - now that I have a max of patients by zip, how do I pull out the City with the Max patients by Zip?

• ###### 3. Re: Calculated field for tooltip display - MAX

That value gets set for every row.  Zip by Zip.  So on any given row you now have a Zip, a City name, a max-for-Zip, and a patient count for that city.  So you can do:

If [Patients] = [max-for-Zip] then [City Name] END

That calc will be set to city name for the one row within the Zip that matters.  All other cities will have a null there.  On a zip-by-zip basis, MAX([top-city calc]) will grab the value in that one row.

• ###### 4. Re: Calculated field for tooltip display - MAX

May need to back track here.  Having trouble with first calculation.  I need to get something equivalent to:  { FIXED [zip code] : MAX(COUNTD([PatientID])) }  aka the Max Count of PatientID per zip code.  Can't use two aggregate functions though.

What am I missing?

• ###### 5. Re: Calculated field for tooltip display - MAX

You can nest LOD calcs.

Either create the FIXED COUNTD calc, and then do another FIXED on that calc, or just nest them.  Inside the MAX( ...)  you can do another { FIXED ...  }