To eliminate entries where sum of values is 0

[Saurabh07]

Hello Gurus,

I have the mentioned data set,

Country	Value
Algeria	10
Andorra	1
Angola	0
Anguilla	0
Algeria	2
Andorra	9
Angola	0
Anguilla	0
Algeria	7
Andorra	3
Angola	2
Anguilla	0
Algeria	3
Andorra	6
Angola	5
Anguilla	0

Now I am trying to eliminate the country 'Andorra' and all other countries whose sum of Values is 0. I have successfully eliminated 'Andorra' from my pivot table with mentioned expression:

sum({<Country -= {'Andorra'}>}Value), but struggling to achieve the second condition. So my desired output in pivot form has to be;

Country	Sum of Value
Algeria	22
Angola	7

Can anyone please help me out here?

[kaushiknsolanki]

Try this.

sum({<Country = {'=Sum(Value)>0'}>}Value)

[Taoufiq_Zarra]

@Saurabh07  sum of value or one value ?

Angola for example sum of value >0, if you use juste the expression sum({<Country -= {'Andorra'}>}Value) you get the same output

Country	Sum of Value
Algeria	22
Angola	7

[Taoufiq_Zarra]

[Saurabh07]

Hello @Taoufiq_Zarra,thank you for the response. I am not sure how you are getting this output. Below in snap of my pivot table in Qlik;

So here along with Andora, I also wish to eliminate Anguilla and all other countries whose sum of values is 0.

[Saurabh07]

Hello @kaushiknsolanki This looks fine, but I am struggling to accommodate multiple constraints here. I intend to eliminate specific countries (Andorra in this case) along with all other countries for whom sum of values is 0 (Anguilla in this case)

[kaushiknsolanki · Accepted solution]

In that case your expression will be.

Sum({<Country = {'=Sum(Value)>0'}> - <Country = {"Andorra"}>}Value)

[Lisa_P]

In your chart settings in the Properties panel, in Data, Dimensions, Country use the Limitation as per below. Exact value >0

And the result should be:

[Taoufiq_Zarra]

@Saurabh07  you just need sum({<Country -= {'Andorra'}>}Value)  and to unchek this

uncheck

sorry i use french version

[Saurabh07]

@Taoufiq_Zarra  This looks awesome. May I know which Pivot you are using here? the menu which I see for you is slightly different from mine!

And I don't see any option in my menu pane which will allow me to uncheck Zero Values