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: 
tracycrown
Creator III
Creator III

Sum Total with Exclusion

Dear all

Please help to advise how to compute Others (sum of Product D & E only) in the same Revenue Range with Product C and excludes Product A & B in the year of 2019.  

Thank you, Tracy

1 Solution

Accepted Solutions
edwin
Master II
Master II

adding Product to the chart will validate the expression. if you only need the total revenue, just drop the Product dimension

View solution in original post

9 Replies
tresesco
MVP
MVP

Your sample seems giving right result (when one revenue is selected). However, you could try using simpler expressions like:

Exp 1 :  Sum(Data)

Exp 2 :  Sum({<Product=e(Product)>} Data)        // if 'others' means not-selected/not in-scope ones 

tracycrown
Creator III
Creator III
Author

Dear Mr Tresesco

Thanks for your quick advice.

For Product C, I have changed to Exp 1 : sum(Data)

For Others, Exp 2 : sum({<Product=e(Product)>} Data) is NOT CORRECT

because it covers Products D& E (within the same Revenue Range 3,000000-3,999999) and Products A & B (outside the Revenue Range).  Kindly advise how to exclude Products A & B.

 Many Thanks, Tracy

tresesco
MVP
MVP

I don't see any association between your product and range dimensions in data model. With this your solution might not work as expected.

tracycrown
Creator III
Creator III
Author

Dear Tresesco,

Okay, what is your best advice ?

Thanks, Tracy

 

tresesco
MVP
MVP

Correct your data model first.

tracycrown
Creator III
Creator III
Author

Dear Tresesco

Sorry, not sure how to do it.

Tracy

edwin
Master II
Master II

from my understanding of the objective, you want the Revenue of Products with same range as C.  this will automatically exclude A and B as they are not in the same revenue range of C.
one way to do this is first determine the lower and upper range of the Revenue of C and save them in variables:
vLower:  =only({<Type={'Revenue'}, Product={'C'}>} if(Data>=Lower and Data<=Upper,Lower))
vUpper:  =only({<Type={'Revenue'}, Product={'C'}>} if(Data>=Lower and Data<=Upper,Upper))

then in your chart, you can have the Product as Dimension and the following as the expression:

=sum({<Type={'Revenue'},Product-={'C'}, Data={">=$(vLower)<=$(vUpper)"}>}Data)

this will result in products D and E.  if your business rule explicitly says exclude A and B:

=sum({<Type={'Revenue'},Product-={'C','A','B'}, Data={">=$(vLower)<=$(vUpper)"}>}Data)

edwin
Master II
Master II

adding Product to the chart will validate the expression. if you only need the total revenue, just drop the Product dimension

tracycrown
Creator III
Creator III
Author

Dear Edwin

Sorry for the late reply as I was sick and thank you so much for your advice and explanation.

Can you advise how to present the answer as per attached format :

Thank you in advance for your help, Tracy