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

    Only include max/min values in a graph

    martinohlson

      Hi,

      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:

          if(rank(max(Field),4,1)<=3,green())

          or

          if(rank(-max(Field),4,1)<=3,red())

          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.

          Regards.

            • Only include max/min values in a graph
              martinohlson

              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.

                  =max(EmployeeID,3)

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

                  if(EmployeeID>vMaxEmployeeID,EmployeeID)

                  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.

                  Regards.

              • Only include max/min values in a graph

                Try this one:

                if(count(total distinct [YourDimension]) - rank(sum([FieldToBeCalculated]))+1<=3,sum([FieldToBeCalculated]),
                if(rank(sum([FieldToBeCalculated]))<=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(),
                if(rank(sum([FieldToBeCalculated]))<=3,lightblue(),black()))

                Hope that helps