Set Analysis where value is null

[#1]

Hi - I have the associated data set and need to count the sum of 'Sales' for 'Country' where  'Country1' is null indicated with '-' . So the total amount should be 28000.

Also ( or as part of this even ) is there is a way to simply exclude all non NULL values for Country1 so effectively you'd be left with 3 rows of data ie for SPAIN, SCOTLAND AND IRELAND.

Many thanks in advance

[Anonymous]

Try:

Sum({1-$<Country={'*'}>}  Sales)

Marc.

[avinashelite]

please refer the attachment for the solution

[#4]

Thanks Avinash but I think you may have understood. It should only return rows where country = '-'. So only 3 rows of data should be displayed ie IRELAND SPAIN and SCOTLAND.

Thanks

[avinashelite]

check this solution .

NOTE: In the dimension check suppress null

[#6]

This works as it's counting the '-' as a value but remember the - is not a value it's actually a NULL. Sorry should probably have been clearer. So effectively need to return rows where Country 1 is NULL.

[avinashelite]

can please provide some examples .

[#8]

Treat the '-' as NULLS ie blank cells

[avinashelite]

Hi,

But in you example file it not null it has the value - so it will be treated as the - value.

if you have blank then only it will be treated as null.

NOTE: Even if you have a single space also it will treated as value