topic Re: How to display top 3 Contract Names when same contract names are there? in QlikView

How to display top 3 Contract Names when same contract names are there?

srinivas1921 — Thu, 22 Sep 2016 14:27:09 GMT

Hi,

one of our requirement is need to display top 3 contract Names based on amount value in the Bar chart.But when I trying to display top 3 contract Names it was showing wrong values due to grouped by Contract Name.

Please see below tables and suggest me solution on this to expected results.

Actual Data:-

Contract Number	Contract Name	Amount
121	AAA	100
122	BBB	50
123	AAA	62
124	CCC	55
125	DDD	80

showing output in the Bar Chart as below:-

Contract Name	Amount
AAA	162
DDD	80
CCC	55

Expected Output as below:-

Contract Name	Amount
AAA	100
DDD	80
AAA	62

Re: How to display top 3 Contract Names when same contract names are there?

martinpohl — Thu, 22 Sep 2016 14:34:17 GMT

I think you should Change your Expression from sum(Amount) to max(Amount)

So you will get the Maximum number for each Contract Name.

Than you can Limit the datas to top 3.

Regards

Re: How to display top 3 Contract Names when same contract names are there?

sunny_talwar — Thu, 22 Sep 2016 14:39:29 GMT

Check out the attached

Re: How to display top 3 Contract Names when same contract names are there?

miguelbraga — Thu, 22 Sep 2016 14:44:30 GMT

Hey there,

Please check out my solution in the attached file:

Best regards,

D.A. MB

Re: How to display top 3 Contract Names when same contract names are there?

sunny_talwar — Thu, 22 Sep 2016 14:50:19 GMT

miguelbraga‌‌ -

But this isn't matching the desired output.

Re: How to display top 3 Contract Names when same contract names are there?

miguelbraga — Thu, 22 Sep 2016 14:54:42 GMT

Hey Sunny,

I've noticed that, I'm sorry

Your solution is the right one

Thanks mate

Re: How to display top 3 Contract Names when same contract names are there?

sunny_talwar — Thu, 22 Sep 2016 14:57:21 GMT

Don't have to be sorry my friend. I am pointing it out in a hope that you might come up with another way to do this. I don't like what I have, may be someone else can do better

Best,

Sunny

Re: How to display top 3 Contract Names when same contract names are there?

miguelbraga — Thu, 22 Sep 2016 15:06:17 GMT

This should do the work without the aggr function

Best regards

D.A. MB

Re: How to display top 3 Contract Names when same contract names are there?

sunny_talwar — Thu, 22 Sep 2016 15:07:59 GMT

Dimension limits? Sweet

Thanks for sharing miguelbraga‌. But try to give more information as the OP might have to look for what you have done.

Best,

Sunny

Re: How to display top 3 Contract Names when same contract names are there?

miguelbraga — Thu, 22 Sep 2016 15:10:27 GMT

Hey srinivas b

I've done a Straight Table with a Dimension Limit based upon a Variable that you can change with the slider chart object to have a more flexible table

Best regards,

D.A. MB

Re: How to display top 3 Contract Names when same contract names are there?

srinivas1921 — Thu, 22 Sep 2016 15:22:30 GMT

Hi Sunny,

your solution working fine but in my bar chart displaying two dimensions like parent contract name and calculated dimesion. I want to display only Parent contract name only.please suggest.

Re: How to display top 3 Contract Names when same contract names are there?

sunny_talwar — Thu, 22 Sep 2016 15:27:12 GMT

Try this script:

Table:

LOAD Dual([Contract Name], AutoNumber([Contract Number]&[Contract Name])) as [Dim Contract Name],

[Contract Number],

[Contract Name],

Amount

FROM

[https://community.qlik.com/thread/233795]

(html, codepage is 1252, embedded labels, table is @1);

Bar Chart

Dimension

[Dim Contract Name]

Expression

=Sum({<[Dim Contract Name] = {"=Rank(Sum(Amount)) < 4"}>}Amount)

Re: How to display top 3 Contract Names when same contract names are there?

miguelbraga — Thu, 22 Sep 2016 15:28:00 GMT

Hey there,

You want something like this?

Please see the attached file

Best regards,

D.A. MB

Re: How to display top 3 Contract Names when same contract names are there?

miguelbraga — Thu, 22 Sep 2016 15:30:31 GMT

Even better now

Best regards,

D.A. MB

Re: How to display top 3 Contract Names when same contract names are there?

srinivas1921 — Thu, 22 Sep 2016 15:45:35 GMT

Thank u very much Sunny