-
1. Re: How to get min or max values
Claire Smith Sep 11, 2019 11:15 AM (in response to Rahul Rautela)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
Bryce Larsen Sep 11, 2019 11:26 AM (in response to Rahul Rautela) -
3. Re: How to get min or max values
Michael Hesser Sep 11, 2019 11:36 AM (in response to Rahul Rautela)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!
-
4. Re: How to get min or max values
Michael Hesser Sep 11, 2019 11:40 AM (in response to Bryce Larsen)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
Bryce Larsen Sep 11, 2019 11:42 AM (in response to Michael Hesser)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
Vinoth M Sep 11, 2019 1:09 PM (in response to Rahul Rautela)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
Bryce Larsen Sep 11, 2019 1:25 PM (in response to Vinoth M)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.
-
Effeciency_v2018.1.twbx 23.8 KB
-
-
8. Re: How to get min or max values
Vinoth M Sep 12, 2019 2:03 AM (in response to Bryce Larsen)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
Bryce Larsen Sep 12, 2019 9:52 AM (in response to Vinoth M)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.