How to search based on an expression?

[#1]

Hey All,

I am trying to use the search option to filter to the top 100 based on a rank expression. So I have a Description column, and a NS$ Rank Column that is sorted ascending. The NS$ Rank column is searchable and I can type in >=100 and it filters to only the descriptions ranked 1-100. I now need to know how I can do this in a macro. Since it isn't a "Field" it is an expression I am having troubles trying to figure it out. Any suggestions or advice on how I could use the search function to limit this column to just the Top 100 would be great.

Thanks,

Austin

[#2]

Do you want to show this data in a chart, say like the top 10/20 etc..If yes, in that case if you have entered a column as dimension then in chart properties -> Presentation -> Check the Max Visible Number (1-100) check box and enter the no of bars that you want to see.

Similarly if you want to sort either asc/desc select the appropriate in Sort tab.

Hope this helps.

[#3]

I am trying to get it to do Top 100 in a straight table. The only problem I have if I do the Max Visible is that the total row sums up all the records and not just the Top 100. So that is why I am trying to use the Searchable field on the column and put <=100, so that it will give me the top 100 and the total row will only be the total for these 100 records. I still have yet to figure out the syntax for this if anyone has any suggestions.

Thanks,

Austin

[#4]

If i understand you right, just use this formula:

Example: =aggr(if(rank(sum(Revenue))<=100 or rank(-sum(Revenue))<=100,Customer),Customer)

[#5]

I think this is the path that I want to go down, but how would I put this into a macro? I want to be able to sort different columns this way before I export the table. I am not sure if this makes sense or not. I want to use the magnifying glass function in a macro to sort the column >=100. Since I am such a beginner at this the exact syntax would be helpful.

Thanks,

Austin