topic Set Analysis for blank/null or a Value on the same field in data in QlikView

Set Analysis for blank/null or a Value on the same field in data

abhiM — Sat, 16 Nov 2024 01:50:08 GMT

Hi,

Is there a way to return rows in the data that have blank or null or some value in the same column using set analysis?

I am trying to get sum of dollars for Order Year =2019 or Order Year is null or order year is blank

Here is my expression.

SUM({<[ORDER_DATE] -={'*'},[ORDER_DATE] = {'2019*'}>} DOLLARS)

ORDER_DATE is a Date Key field in the data. I cannot make changes to the load script it has to be in the Set expression.

Can someone please help?

Thanks

Re: Set Analysis for blank/null or a Value on the same field in data

m_woolf — Wed, 27 Nov 2019 16:07:27 GMT

Maybe:

SUM({<[ORDER_DATE] -={'*'}> + <[ORDER_DATE] = {'2019*'}>} DOLLARS)

Re: Set Analysis for blank/null or a Value on the same field in data

Brett_Bleess — Tue, 10 Dec 2019 14:56:36 GMT

Have a look at the following Design Blog post, it may be of some help if the other post did not work, but if that post did work, be sure to circle back to the thread and use the Accept as Solution button on the post to give the poster credit for the assistance and let other Community Members know it worked.

https://community.qlik.com/t5/Qlik-Design-Blog/Excluding-values-in-Set-Analysis/ba-p/1471704

Here is another main one that has some other links in it:

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

Regards,
Brett