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