topic Re: Set Analysis where value is null in QlikView

Set Analysis where value is null

Wed, 11 Feb 2015 14:54:45 GMT

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

Re: Set Analysis where value is null

Anonymous — Wed, 11 Feb 2015 14:56:36 GMT

Try:

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

Marc.

Re: Set Analysis where value is null

avinashelite — Wed, 11 Feb 2015 15:05:11 GMT

please refer the attachment for the solution

Re: Set Analysis where value is null

Wed, 11 Feb 2015 15:09:55 GMT

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

Re: Set Analysis where value is null

avinashelite — Wed, 11 Feb 2015 15:12:05 GMT

check this solution .

NOTE: In the dimension check suppress null

Re: Set Analysis where value is null

Wed, 11 Feb 2015 15:18:26 GMT

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.

Re: Set Analysis where value is null

avinashelite — Wed, 11 Feb 2015 15:20:12 GMT

can please provide some examples .

Re: Set Analysis where value is null

Wed, 11 Feb 2015 15:28:27 GMT

Treat the '-' as NULLS ie blank cells

Re: Set Analysis where value is null

avinashelite — Wed, 11 Feb 2015 15:33:36 GMT

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