Only include max/min values in a graph

[#1]

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?

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

[#3]

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

[#4]

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)?

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