Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

arjunmadhusudan
New Contributor II

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

Re: exclude selection and find top ranked sales

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?

9 Replies

Re: exclude selection and find top ranked sales

Try this

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

arjunmadhusudan
New Contributor II

Re: exclude selection and find top ranked sales

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.

Re: exclude selection and find top ranked sales

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

arjunmadhusudan
New Contributor II

Re: exclude selection and find top ranked sales

Sunny,

Unfortunately this expression is not working.

Re: exclude selection and find top ranked sales

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
New Contributor II

Re: exclude selection and find top ranked sales

Sunny,

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

Re: exclude selection and find top ranked sales

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
New Contributor II

Re: exclude selection and find top ranked sales

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.

Re: exclude selection and find top ranked sales

This should give you the company

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


Capture.PNG

Community Browser