topic Re: Sum Total with Exclusion in QlikView

Sum Total with Exclusion

tracycrown — Thu, 07 Jan 2021 04:00:53 GMT

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

Re: Sum Total with Exclusion

tresB — Thu, 07 Jan 2021 05:27:36 GMT

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

Re: Sum Total with Exclusion

tracycrown — Thu, 07 Jan 2021 05:51:34 GMT

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

Re: Sum Total with Exclusion

tresB — Thu, 07 Jan 2021 06:03:32 GMT

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

Re: Sum Total with Exclusion

tracycrown — Thu, 07 Jan 2021 06:11:39 GMT

Dear Tresesco,

Okay, what is your best advice ?

Thanks, Tracy

Re: Sum Total with Exclusion

tresB — Thu, 07 Jan 2021 06:15:51 GMT

Correct your data model first.

Re: Sum Total with Exclusion

tracycrown — Thu, 07 Jan 2021 07:55:37 GMT

Dear Tresesco

Sorry, not sure how to do it.

Tracy

Re: Sum Total with Exclusion

edwin — Thu, 07 Jan 2021 15:18:08 GMT

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)

Re: Sum Total with Exclusion

edwin — Thu, 07 Jan 2021 15:19:11 GMT

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

Re: Sum Total with Exclusion

tracycrown — Thu, 14 Jan 2021 15:56:07 GMT

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