Get a list of all items except a few

[#1]

Hi

I got 3 tables

Country codes (having the value e.g. US)

City codes and names (like NYC, "New York", WAS, "Washington" etc) - linked to counties

Then i got a measure table with the turnover linked to the city names

Now i want to make one row in a table where i sum the turnover for the country (US) except the cities (New and Washington)

I define this in the Dimension in the table and then calculate the turnover in an expression (the expression works fine as long i hav found the right way to define the dimension)

I have worked a bit with this formula, but it dont quite work

=if( if( Match(CityCode,'WAS','NYC')>0, Null(), CountryCode),='US')

Anyone that have an idea to get this working?

Thanks...

Flemming

[israrkhan]

try like below:

Sum({<Country = {'US'}, City = {*} - {'[New York]', 'Washington'}>} turnover)

this will sum the turnover for all city in US , Except New York and Washington.

[#3]

Try using set analysis in the expression, e.g.

Sum({$<city-={'WAS'},city-={'NYC'},country={'US'}>} turnover)

[#4]

HI

I was more looking of a solution that could do the same as a dimension, not as an expression.

How will that look like?

[israrkhan]

you want this total in dimension,

if you will use this expression in dimension then you will get the amount as dimension.

and in this case, dimension does make use.

and you can get the same by using

Sum({1<Country = {'US'}, City = {*} - {'[New York]', 'Washington'}>} turnover), i used 1 in expression to disregard all selections...

[#6]

Bear in mind that calculated dimensions affect performance. Avoid if you can.