4 Replies Latest reply: Jan 4, 2011 6:29 PM by Karl Pover RSS

    Only include max/min values in a graph



      I want to write an expression so I can find some of the lowest and highest values in a data collection.

      Say I got the following data:

      2, 2, 3, 5, 6, 6, 7, 7, 8, 9

      What do I write to only include the bottom three (2, 2, 3) or top three (7, 8, 9) values in a graph?

      I know how to make it by just sorting by value and only showing a maximum of 3 values in a graph, but that doesn't really eliminate the rest of the values, it's just hiding them.

      Is there a way to do it using the max() function and calling the max() value and the 2 values next to it?

      One place I'd like to use it at is to mark the bottom 3 values red in a pivot table .


      Also I wonder how do I enable a pivot table to be manually sorted by clicking on the different labels in it?

        • Only include max/min values in a graph
          Karl Pover

          You can use something like the following expression:




          And no, there isn't a way to enable manual sorting in a pivot table, but there might be workaround in some cases since you can sort a pivot table by an expression and an expression can be a variable that you can allow the user to change by using actions or a macro. This would only work for sorting by expressions values.


            • Only include max/min values in a graph

              Hey thanks for the answers.

              It looks good but I can't get it to work in my situation.

              Another question regarding MAX that might sort things out:

              If I want to use a dimension (say EmployeeID) but I only want to include the newest employees (highest EmployeeID), is there a way to use the max function on my dimension to only include the highest or the 3 highest EmployeeID's?

              There is a button in the Dimension window called "Add calculated dimension", is that the right place to add my code and what should i write?

              The only thing I've figured out so far is that it should be something like "=MAX([EmployeeID])" but that doesn't seem to work....


              Another example to clarify: If I've got two dimensions, EmployeeID and Year and the expression sum(sales), I get Employee 1 2 3 4 in 2006, Employee 1 2 3 4 in 2007 etc. If I only want to get Employee 1 2 3 4 in 2006 (not in the other years) or vice versa; only employee 1 in all the years, how do I add a Max() function to strip year/employeeID to only contain a part of the original values (in this case only 2006 from the Year table that usually contains 2006-2010, or only "1" from the EmployeeID table that usually contains 1-4)?

                • Only include max/min values in a graph
                  Karl Pover

                  The construction of a calculated dimension is somewhat different than the construction of an expression. You could create a variable called vMaxEmployeeID like the following which returns the third largest EmployeeID value.


                  And then in the calculated dimension put something like the following.


                  You might want to look into bookmarks, too. You can save a bookmark where the selection is an expression like the one below. This way you don't have to battle to fix a QV report.

                  =EmployeeID>=max(total EmployeeID,3)

                  Notice that you should add the total keyword in this search and also when you are using max(EmployeeID) in a chart with EmployeeID as a dimension.


              • Only include max/min values in a graph

                Try this one:

                if(count(total distinct [YourDimension]) - rank(sum([FieldToBeCalculated]))+1<=3,sum([FieldToBeCalculated]),

                It will calcule the 3 lowest and highest values.

                Then you could use the next expression to colour text:

                if(count(total distinct [YourDimension]) - rank(sum([FieldToBeCalculated]))+1<=3,lightred(),

                Hope that helps