Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
USe Rank() in dimension
Regards
PFA Sample
Vikas
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
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
Try This IF(Aggr(Rank(Sum(Sales),Field)<=50,Fields..)
Regards
Chaitanya
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
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
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.
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.