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

    How to get min or max values

    Rahul Rautela

      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".

       

      Thanks in advance.

       

                                                                    

      MonthABC(Calculated   field=B/A)
      Jan2229%
      Feb33412%
      Mar5559%
      Apr6635%
      May8867%
      Jun44818%
      July6635%
        • 1. Re: How to get min or max values
          Claire Smith

          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

            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
              Michael Hesser

              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

                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

                  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

                    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 tableCalculated Field
                    MonthValue AValue BOverall Efficiency
                    Jan1020200%
                    Feb20525%
                    March30827%
                    April402153%
                    May501530%
                    June6075125%

                     

                    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

                      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
                        Vinoth M

                        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

                          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.