topic Pivot Table - Creating a Restriction in Display of a Dimension in QlikView

Pivot Table - Creating a Restriction in Display of a Dimension

Wed, 14 Oct 2009 19:57:26 GMT

Hi everyone, I have a pivot table which has:

time spanning across the top
a list of systems going down
and an expression sum(impact) which populates the cells.

Is there a way I could possibly limit the number of systems going down to the top 10? I can't seem to find a way in the pivot table. The systems are currently ordered by the descending total of the expression: sum(impact).

Would set analysis in some way solve this problem as when I create the table it shows all the systems available within the selection I have made. Or should I make a calculated dimension?

If you could help it would be much appreciated. Thanks.

Pivot Table - Creating a Restriction in Display of a Dimension

Wed, 14 Oct 2009 20:07:34 GMT

Hello

Try something like this in u r dimensions

=If(Aggr(Rank(Sum(impact),[systems ])<11,[systems ],'All Other ')

and keep the other same as it is

It should work

Talha

Pivot Table - Creating a Restriction in Display of a Dimension

Wed, 14 Oct 2009 20:21:56 GMT

Hi Talha,

Thanks for your response. I've tried that expression but I get the 'error in expression' comment. I think there may be a problem with the brackets but i can't see what it is!

Pivot Table - Creating a Restriction in Display of a Dimension

Wed, 14 Oct 2009 21:43:45 GMT

Can you send me the exact Field names or else one application with the mockup data in that.

Talha

Pivot Table - Creating a Restriction in Display of a Dimension

Wed, 14 Oct 2009 23:36:11 GMT

Fieldnames:

Date: MonthYear

The column from top to bottom is: SystemName

Expression: sum(Impact)

Regards

Pivot Table - Creating a Restriction in Display of a Dimension

Wed, 14 Oct 2009 23:48:18 GMT

Try this out

=If(Aggr(Rank(Sum(Impact)),SystemName)<11,SystemName,'All Other ')

This should work

Talha