topic Re: Tricky Aggregation in top 10 Values in QlikView

Tricky Aggregation in top 10 Values

sasikanth — Mon, 10 Nov 2014 07:54:58 GMT

Hello Guys here i have a special requirement ,

I have the following fields

ID , country, region, Cust_cd and year, Sales

Here i want to calculate top 10 customers vs Other customers based on year and country

my formula is

=sum({<Cust_cd={$(=concat(if(aggr(rank(sum(sales),4),cust_cd)<=10,chr(39)&Cust_cd&chr(39)),','))}>}Sales)

and in a bar chart i am taking year and Country as Diemensions ,

when i made any selections on seperate list boxes country and year , then only it is giving correct values,

if i did not select any value then it is showing some other values ,

the problem i have identified is when taking top 10 cust_cd , for the first time it is taking based on total COUNTRY field,

if you select a specific country then only it is showing top 10 values in that country,

as i am taking country as dimension , the chart has to show top 10 valeus accordingly ,

can you guys please help in sloving this problem , please let me know where i am doing mistake ...

Thanks

Sasi

Re: Tricky Aggregation in top 10 Values

SergeyMak — Mon, 10 Nov 2014 07:58:22 GMT

Hi,

Can you share your app or sample of your data

Regards,

Sergey

Re: Tricky Aggregation in top 10 Values

sasikanth — Mon, 10 Nov 2014 08:03:24 GMT

soryy i cannot ,

simple i am taking County as Dim and the above exp as EXPRESSION,

by default it is taking top 10 CUST based on all the countries , if i select on specific county then top 10 based on that country it is taking ,,,

Re: Tricky Aggregation in top 10 Values

SergeyMak — Mon, 10 Nov 2014 08:11:06 GMT

It's hard to say.

Just create a spreadsheet with some dummy data just to save the time and get result faster....

Re: Tricky Aggregation in top 10 Values

djsampat — Mon, 10 Nov 2014 08:26:03 GMT

For top 10 use this:

rangesum(Top(sum(Amount),1,10))

For all products use this:

sum(total Amount)

try this

if(aggr(Rank(Sum(sales),10),region)<=10,aggr(Sum(sales),region,country))

If this helped you, please mark as Helpful. If it solves your issue, please mark as Answer

Regards
Dhruv

Re: Tricky Aggregation in top 10 Values

sasikanth — Mon, 10 Nov 2014 09:09:15 GMT

Not working

Here top 10 custmers i want , if based on region how can i get top 10 and others ?

Re: Tricky Aggregation in top 10 Values

SergeyMak — Tue, 11 Nov 2014 06:28:11 GMT

Hi,

There is no Country field in your data.

What should I use instead of?

Regards,

Sergey

Re: Tricky Aggregation in top 10 Values

sasikanth — Tue, 30 Dec 2014 11:27:29 GMT

Can any body please help on this...

I have few Regions(4) and Cust_code, i need to calculate /create a barchart like

For every year top 10 values of the region
please check the image once

i have tried using Expression using

sum({<CUST_CD={$(=Concat(if(aggr(rank(sum (SALES ),CUST_CD)<=10,chr(39) & CUST_CD & chr(39)),','))}>}SALES)

Note: here common codes are there between Regions ,

Thanks

Sasi

Re: Tricky Aggregation in top 10 Values

sasikanth — Tue, 30 Dec 2014 11:31:29 GMT

any body?

Re: Tricky Aggregation in top 10 Values

djsampat — Mon, 05 Jan 2015 07:49:17 GMT

Please upload a sample Qvw so we can work with your data as an example.

Re: Tricky Aggregation in top 10 Values

djsampat — Mon, 05 Jan 2015 08:04:38 GMT

View attached sample. Replace the inline data with more lines if necessary or replace the Company and Region with familiar names.

Top 3 Sales by Region Sample - Inline

Re: Tricky Aggregation in top 10 Values

djsampat — Mon, 05 Jan 2015 08:07:57 GMT

Why don't you try limiting the Dimension by Largest 10 and the expression is the Region Sales only.

If this helped you, please mark as Helpful. If it solves your issue, please mark as Answer

Regards

Dhruv