Eliminating dimensions with a sum of different field of 0

[#1]

Hello, I thought I had this one but I have apparently missed something.  I have a list of all ISO listed countries (200+) that I use to convert, say NO to Norway or US to United States to make it easier for management.  The expression I used is a sum of the amount spent in a country.  Needless to say, we do not have spend in every country.  So I basically put the country name as a dimension with a statement summing the spend in the country and not showing countries where spend = 0 or null.  I THOUGHT it worked as it eliminated all countries where we had zero spend. If I click on a country to see spend in that country, that is fine.  BUT, all other countries disappear from the list (Chart Object, straight table) so 1. I cannot add a country unless I clear it all and 2. it makes it difficult when you select another criteria and the other countries have disappeared.  Any ideas?  Thanks in advance!!!

[Gysbert_Wassenaar]

Enable the Suppress Zero-Values option on the Presentation tab of the properties window of the chart. That should be enough to suppress the zero sum countries.

[#3]

Sorry, I may have stated it wrong but I have no problem eliminating zeros. he problem is that it does not keep the rest of the countries as undetected so they can add one later. I can give a screenshot tomorrow (I am in Europe) but if I select, say Canada, for example, the other selections disappear for country. So I cannot select Canada, check out the results then add,say France as can do with other dimension tables. Thanks, Dave

Sent from my iPhone

[stigchel]

If I understand you correctly, you're selecting in the chart object. Selecting in the chart object, selects values in the dimension. The expression results are then restricted to that, in this case, country. Either use an expression that will ignore the selection in country e.g. Sum({<Country=>} Spend) or add a listbox object to the sheet with the field Country to do the selections.

[#5]

Ah interesting and makes sense. I will check at the office tomorrow. Thanks!

Sent from my iPhone

[#6]

Almost there and thank you Piet I now get the full list to remain but cannot select multiple countries.  It also shrinks back to hiding the nulls if I select a different criteria (say Year) but I can live with that.  But I need to make multiple selections to show, for example "Scandanavia" by clicking on the Scandanavian countries.

Many thanks in advance,

Dave

[stigchel]

You can select multiple countries by selecting an area in the chart, with your mouse click and hold and 'draw' the area you wish to select. You can make non-adjacent selections by pressing and holding the Ctrl key, make multiple selections and release the Ctrl key. A listbox with e.g. windows checkbox selection style (presentation tab) would make it more intuitive to do these selections

[#8]

Hello again.  I think I am almost there but what I want is to take a table that has 250 or so countries and when a second field has a total sum of zero, I do not want to show that Country at all.  I have gotten this but then when 1 country is selected, then the rest disappear.  What I would like is to have it more like Business Unit.  If I select one there, it is highlighted and the rest remain.  I can get list of only ones that are non-zero but the behavior (no matter what I use, list, chart, etc) has this undesired behavior.

Thanks,

Dave

[stigchel]

If I understand you correctly, you want to ignore the selection in country, then in your expressions use e.g.

Sum({<Country=>} Spend)

That will ignore the selection in country.

You can highlight the selected country by using the background color option of the expression (click on the plus sign in front of the expression to expand) and use something like

if(Country='$(=Country)', rgb(255,0,0))

[#10]

Actually, what I am looking to do is much easier, I think.  I basically want a list box where only countries, for example, where we have clients (some sum greater than zero) to be shown.  That I had, but when I selected one, all the non-selected countries would disappear.  Normally, when I click on a selection, it goes to the top and becomes "activated" and if I want to add a second country, a can ctrl-click it and that too becomes selected.   Unfortunately, when I select one, all of the other ones disappear.  So i must clear to select others.  So if I wanted to create a subtotal for Norway, then Scandanavia, then Europe, I would have to clear the selection each time as the non-selected countries (i.e, not Norway, or then not Scandanavia using ctrl-click) would be gone from the list and I have only the selected ones visible.  Thanks, Dave