Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
In a calculated listbox [see very bottom on the filed list]
aggr(if(sum({1}Spend)>0,Country),Country)
The problem is not juts getting the 0s out, but leaving the ones with values in the box to be selected later.
Here it is unfiltered. If I select one:
All other countries are gone. Even with a sum above zero...I am hoping to get it more like this category box:
Where the top two are selected and I can ctrl-click other selections to add or remove as needed, while keeping all the countries with spend available.
Thanks again!
aggr(if(sum({1}Spend)>0,only({1}Country)),Country)
Beautiful Steven! 1000 thanks! Makes life so much easier than reselecting each time!
Can you set as Correct, Thanks
Done. Thanks again.