Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
arjunmadhusudan
Contributor III
Contributor III

exclude selection and find top ranked sales

Hi,

I have data as below

Company    Sales

A                  1000

A                     400

B                    500

B                      20

C                  2000

D                    400

D                      20

I need to calculate     Sum of sales selected company  divided by   Sales of company with highest sale from the remaining list ( not selected)

Regards,

Arjun

1 Solution

Accepted Solutions
sunny_talwar

When I select D, I get this

Capture.PNG

Where D is 420 and C is the top one which is 2000... and 420/2000 = 0.21... do you agree?

View solution in original post

9 Replies
sunny_talwar

Try this

=Sum(Sales)/Max({<Company = e(Company)>}Aggr(Sum({<Company = e(Company)>}Sales), Company))

arjunmadhusudan
Contributor III
Contributor III
Author

This does not give me any output without selection. It is only after selection that I get some output. Is that correct ?

Also I need to verify the output on selection using this expression.

Thanks.

sunny_talwar

Yes, what output do you expect to see without selection?

arjunmadhusudan
Contributor III
Contributor III
Author

Sunny,

Unfortunately this expression is not working.

sunny_talwar

I am afraid, you will have to provide more information... what do you mean it is not working? Showing nulls? Showing 0s? Computer Shutting Down?

arjunmadhusudan
Contributor III
Contributor III
Author

Sunny,

I am getting sum of all companies rather than top sales company excluding selection. Please check.

sunny_talwar

When I select D, I get this

Capture.PNG

Where D is 420 and C is the top one which is 2000... and 420/2000 = 0.21... do you agree?

arjunmadhusudan
Contributor III
Contributor III
Author

Sunny,

I checked again, it seems correct.

I also need to display of this company which is being used in denominator ( output of max expression).

Please help.

sunny_talwar

This should give you the company

=FirstSortedValue({<Company = e(Company)>} Company, -Aggr(Sum({<Company = e(Company)>}Sales), Company))


Capture.PNG