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!

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

Hi Rahul;

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

I had two discrete values:

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!

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

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

Thanks Everyone,

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

Here is our data

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

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.

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.

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.