Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
In that case your expression will be.
Sum({<Country = {'=Sum(Value)>0'}> - <Country = {"Andorra"}>}Value)
@Saurabh07its a sample table
or you can always do :
in Country dimension use :
if(aggr(sum(Value),Country)>0,Country) and uncheck include null value
and in measure sum({<Country -= {'Andorra'}>}Value)
Try this.
sum({<Country = {'=Sum(Value)>0'}>}Value)
@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 |
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.
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)
In that case your expression will be.
Sum({<Country = {'=Sum(Value)>0'}> - <Country = {"Andorra"}>}Value)
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:
@Saurabh07 you just need sum({<Country -= {'Andorra'}>}Value) and to unchek this
uncheck
sorry i use french version
@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