How do I restrict my display to the first n rows in a pivot table?

[justin_morley]

Hi,

Browsing the forums I can find plenty of answers for displaying Top N rows for a given dimension, my question is subtley different.

I would like to restrict my pivot table to only displaying the first 50 rows (for performance reasons) given the current sort. Is there any way of doing this?

Thanks in advance,

Justin

[PrashantSangle]

Hi,

USe Rank() in dimension

Regards

[vikasmahajan]

PFA Sample

Vikas

[markodonovan]

Hi Justin,

As Vikas has shown an example using AGGR and RANK.

This document is well worth a read if you have not seen it before:

QlikView Technical Brief - AGGR

Thanks

Mark

http://www.techstuffybooks.com

[Gysbert_Wassenaar]

The problem is defining what 'first' means. Perhaps forcing the user to make selections first will work. That can be done fairly easily with a calculation condition. See this discussion: Conditional show Straight table based on number of rows

[ychaitanya]

Try This IF(Aggr(Rank(Sum(Sales),Field)<=50,Fields..)

Regards

Chaitanya

[justin_morley]

Thanks Vikas, very helpful.

However I think my scenario is slightly different - your example uses rank based on a sum(SAL). There is no equivalent value to SAL in my pivot table, nothing I can aggregate on.

I guess the furthest right dimension is the thing I want the restriction on, but I don't think I can use rank against that.

Here is a contrived example of my data set (In reality I have >100 calculated dimensions and >10,000 rows!)

Dim1	Dim2	Dim3 - val1	Dim3 - val2
A	1	Expression	Expression
A	2	Expression	Expression
B	1	Expression	Expression
B	2	Expression	Expression

How do I get the first three rows from the above table?

The expression contains no aggregation, and indeed the contents of the expression should not be aggregated upon.

The data is sorted by Dim1 then Dim2 then Dim3, and the data pivoted on Dim3

Does this make more sense?

Thanks,

Justin

[justin_morley]

Thanks Gysbert. I'm already forcing the user to make many, many selections before the grid is shown. The purpose of the grid is really to QC data prior to export. However, it would be helpful to do the QCing step against a reduced data set - I really can't force them to reduce it any further in any meaningful way.

For all the suggestions based on rank(), I believe rank has to accept an aggregating function, and there's no aggregation at all going on in my pivot table, see above for a reduced example.

Thanks

[Gysbert_Wassenaar]

If it's meant for export, can't you use a straight table instead of a pivot table? Straight tables have an option Max Number (1-100) on the Presentation tab that you can use to limit the records shown to 100 or less.

[justin_morley]

Sadly not - the pivot is very important. In the pivoted dimension there are 0-n values, where n could anything up to a few hundred. Long and skinny would be exceptionally difficult to navigate and work out what's going on in the data.