Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

stantrolav
Contributor II

Set analisys for top values in pivot table

How can I bring TOP 8 values ​​for the given expression and 2 additional values ​​specified manually in the pivot table? I think i should use set analisys in dimension or expression.

I got 25 values of Product. To every product i have Amount. In pivot tabe i used Product as dimension and expression sum(Amount).

How can i get something like this:

Productsum(Amount)
INHALIPT2356
ANTI ANGIN2145
HEXORAL1876
CAMETON1500
STOPANGIN1400
TANTUM VERDE1238
STREPSILS786
FALIMINT652
SEPTOLETE23
STREPSILS PLUS6
Others769

Could someone help with this simple task?

1 Solution

Accepted Solutions
Not applicable

Re: Set analisys for top values in pivot table

In the if statement, add your 2 products:

 

=

Aggr(If(Rank(Sum([Amount]))<11 or Product='XXX' or Product='YYYY', Product, 'Others'), Product)

In other words, either your product belong to the top 10 or is XXXX or is YYYY you will get it, if not it will be sum up in Others

Fabrice

10 Replies
MVP
MVP

Re: Set analisys for top values in pivot table

If you can use staright table, you get option of 'Others' in dimension limt tab, and get to use restricting top values.

Not applicable

Re: Set analisys for top values in pivot table

Hi Stanislav,

You can use the  "Dimension Limit" tab to display only 8 values.

Don't need Set analysis for that

best regards

Chris

stantrolav
Contributor II

Re: Set analisys for top values in pivot table

There no Dimension Limit for Pivot table. I cant use "staright table". Sorry tresesco

I cant use "straight table" because I have 2 dimensions. Forgot to mark it. My second dimension is Years. In the end i want to see Top 10 Products (we'll use sum(Total Amount) only for Product dimension) for every year in 1 table.

israrkhan
Valued Contributor II

Re: Set analisys for top values in pivot table

try Calculated Dimension and Use Rank function in Product to get top 10 Rank.

and no idea about others.

MVP
MVP

Re: Re: Set analisys for top values in pivot table

PFA for sample.

Use expression like:

=Aggr(If(Rank(Sum(Amount))<11, Product, 'Others'),  Product, Year)

as calculated dimension for Product for your case and the in sort tab, use Sum(Amount) ->descending

stantrolav
Contributor II

Re: Set analisys for top values in pivot table

But how to make "Others" to be in last position of table?

MVP
MVP

Re: Re: Set analisys for top values in pivot table

In the sort tab use expression something like:

=Aggr(If(Rank(Sum(Amount))<11, Sum(Amount),-1),  Product, Year)

stantrolav
Contributor II

Re: Re: Set analisys for top values in pivot table

It's not full answer as I guess.

How can I get 2 additional values ​​specified manually in my table?

Not applicable

Re: Set analisys for top values in pivot table

In the if statement, add your 2 products:

 

=

Aggr(If(Rank(Sum([Amount]))<11 or Product='XXX' or Product='YYYY', Product, 'Others'), Product)

In other words, either your product belong to the top 10 or is XXXX or is YYYY you will get it, if not it will be sum up in Others

Fabrice

Community Browser