topic How to search based on an expression? in QlikView

How to search based on an expression?

Fri, 28 Jan 2011 21:47:59 GMT

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

How to search based on an expression?

Sat, 29 Jan 2011 08:46:47 GMT

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.

How to search based on an expression?

Mon, 31 Jan 2011 14:43:04 GMT

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

How to search based on an expression?

Mon, 31 Jan 2011 16:37:45 GMT

If i understand you right, just use this formula:

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

How to search based on an expression?

Tue, 01 Feb 2011 16:14:25 GMT

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