9 Replies Latest reply on Sep 12, 2019 9:52 AM by Bryce Larsen

# How to get min or max values

Hi All,

In the below sheet i want to get max or min values from column 'C' & to be used some where in tool tip or in dashboard text.

Requirement is i want to use max value or min value of Column C & to be used in inside fields <max>, <min>

While writing max or min function on column 'C', it throws some error "Can't mix aggregate function with max".

 Month A B C(Calculated   field=B/A) Jan 22 2 9% Feb 33 4 12% Mar 55 5 9% Apr 66 3 5% May 88 6 7% Jun 44 8 18% July 66 3 5%
• ###### 1. Re: How to get min or max values

Rahul,

If this suggestion doesn't work, please attache a workbook so we can better assist you.  I'm assuming in your calculation C it's written like SUM(A)/SUM(B).  Try writing it as just [A]/[B] and see if that clears your aggregation error.  Thanks!

• ###### 2. Re: How to get min or max values

Try using WINDOW_MAX([C]) and WINDOW_MIN([C]) to get max and min of an aggregated field.

• ###### 3. Re: How to get min or max values

Hi Rahul;

If Claire's suggestion doesn't work, you might try going with a WINDOW_MIN & WINDOW_MAX approach.

SUM(Quantity)

SUM(Sales)

I was able to calculate Avg Price (which would be the equivalent to your C column) this way:

Avg Price

SUM([Sales])/SUM([Quantity])

I dropped it on my rows as a discrete element (blue pill).

To pull the MIN and MAX of these calculated values, I made two more calcs:

Window Max

WINDOW_MAX([Avg Price])

//Note: no aggregation needed

Window Min

WINDOW_MIN([Avg Price])

//Note: no aggregation needed

I decided to sort these by Avg Price, as well as list the Min & Max.

Again, if Clair hasn't solved it for ya, perhaps this will give you something to think about!  Happy vizzing!

• ###### 4. Re: How to get min or max values

Bryce: maybe I should write shorter descriptions? I didn't see your suggestion of using WINDOW_MIN & WINDOW_MAX, else I might not have written my novel

• ###### 5. Re: How to get min or max values

Hah, it can be helpful at times, though! And always good to show real use cases.

• ###### 6. Re: How to get min or max values

Thanks Everyone,

I'm also working with rahul on this. I wish to expand little more on rahul's post.

Here is our data

 Data in table Data in table Data in table Calculated Field Month Value A Value B Overall Efficiency Jan 10 20 200% Feb 20 5 25% March 30 8 27% April 40 21 53% May 50 15 30% June 60 75 125%

We need output as below(Please check the red text  and right side requirement).

We’re trying to use the formula as below

MAX(Overall Efficiency)

MIN(Overall Efficiency)

But it is showing the error as we can’t use Max and MIN in aggregate function. Any suggestion on this

Value A vs   Value B
Maximum Overall Efficiency % : 200%
Overall Efficiency Month:
Jan
Minimum Overall Efficiency % :
25%
Minimum Overall Efficiency Month:
Feb
• ###### 7. Re: How to get min or max values

You'll still need to use the approach we suggested: WINDOW functions.

You could do this using LOD Expressions, but I like to avoid these whenever possible. So, I did some fun/tricky things that can probably be done in a quicker manner. But hopefully you can work through the attached.

• ###### 8. Re: How to get min or max values

Thanks a lot, Bryce

I got it where I was wrong. How did you hide & showing only one box instead of 6(for each month). Also, I need to enable only the relevant box.

• ###### 9. Re: How to get min or max values

I used the function LAST()=0 to determine which was the last row. Everything else returned False, so right click on False and select 'Hide'. Then, of course, click on the LAST()=0 and uncheck Show Header.