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

Set analisys for top values in pivot table

stantrolav — Tue, 29 Oct 2013 11:18:08 GMT

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:

Product	sum(Amount)
INHALIPT	2356
ANTI ANGIN	2145
HEXORAL	1876
CAMETON	1500
STOPANGIN	1400
TANTUM VERDE	1238
STREPSILS	786
FALIMINT	652
SEPTOLETE	23
STREPSILS PLUS	6
Others	769

Could someone help with this simple task?

Re: Set analisys for top values in pivot table

tresB — Tue, 29 Oct 2013 11:23:42 GMT

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

Re: Set analisys for top values in pivot table

Tue, 29 Oct 2013 11:24:00 GMT

Hi Stanislav,

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

Don't need Set analysis for that

best regards

Chris

Re: Set analisys for top values in pivot table

stantrolav — Tue, 29 Oct 2013 11:31:35 GMT

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.

Re: Set analisys for top values in pivot table

israrkhan — Tue, 29 Oct 2013 11:39:32 GMT

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

and no idea about others.

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

tresB — Tue, 29 Oct 2013 11:47:43 GMT

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

Re: Set analisys for top values in pivot table

stantrolav — Tue, 29 Oct 2013 12:01:46 GMT

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

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

tresB — Tue, 29 Oct 2013 12:37:21 GMT

In the sort tab use expression something like:

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

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

stantrolav — Wed, 30 Oct 2013 13:41:50 GMT

It's not full answer as I guess.

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

Re: Set analisys for top values in pivot table

Wed, 30 Oct 2013 13:59:13 GMT

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

Re: Set analisys for top values in pivot table

stantrolav — Wed, 30 Oct 2013 14:12:28 GMT

That's it. Ok. Here is final answer from FABRICE44

Dimension:

=Aggr(If(Rank(Sum([Amount]))<=8 or Product='microscope' or Product='backgammon', Product, 'Others...'), Product)

Sort order:

=Aggr(If(Rank(Sum([Amount]))<1000, Sum([Amount]),-1), Product)

We use 1000 in sort order to specify that Others... should be the last from at least of 1000 values.