topic Re: Count Null by Set Analysis in QlikView

Count Null by Set Analysis

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi Guys,

I am trying to count Null in expression and using the below statement. but did not get luck to resolve it. I tried couple of different way by set analysis. Could you please advise me about this?

=Count({$<Year=, Month=, Sale ={'=Len(Trim(Sale))=0'} , YearMonth={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),0)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),0)),'YYYYMM'))"}>} Sale)

Kind regards

Farrukh

Re: Count Null by Set Analysis

dineshm030 — Fri, 09 Dec 2016 10:18:15 GMT

Can you provide the sample QVW file?

Re: Count Null by Set Analysis

marcus_sommer — Fri, 09 Dec 2016 10:26:20 GMT

You could try something like this:

=Count({$<Sale -={'*'}>} Sale)

For testings without further conditions to simplify the approach and avoiding potential errors in other parts - and using from normal selections to reduce the amount of data respectively to emulate the conditions.

But are there really NULL's within the field Sale? If Sale is an field from a common transaction-table there will be quite probably just no records instead of records with NULL. To visualize NULL's is often quite complex - a very good starting point could you find here: NULL handling in QlikView.

- Marcus

Re: Count Null by Set Analysis

sunny_talwar — Fri, 09 Dec 2016 10:26:39 GMT

Try something like this:

=Count({$<Year=, Month=, UniqueIdenfierFieldHere = {"=Len(Trim(Sale))=0"} , YearMonth ={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),0)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),0)),'YYYYMM'))"}>} Sale)

Or you can create a flag in the script

LOAD Sale,

If(Len(Trim(Sale)) = 0, 1, 0) as SaleFlag

...

and then use like this:

=Count({$<Year=, Month=, SaleFlag = {1}, YearMonth ={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),0)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),0)),'YYYYMM'))"}>} Sale)

Re: Count Null by Set Analysis

vinieme12 — Fri, 09 Dec 2016 10:41:02 GMT

Try

=Count({$<Year=, Month=, Sale ={'=isnull(Sale)=-1'} , YearMonth={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),0)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),0)),'YYYYMM'))"}>} Sale)

Re: Count Null by Set Analysis

Anonymous — Fri, 09 Dec 2016 12:49:00 GMT

Hi Sunny,

Could you please advise me related to UniqueIdentifierField, I am using couple of dimensions, eg (MonthYear, Region, Company, Distrct). or do I need to use one of the dimension as an uniqueidentifierfield.

Farrukh

Re: Count Null by Set Analysis

sunny_talwar — Fri, 09 Dec 2016 13:01:11 GMT

I am looking for a field which uniquely defines each row in your fact table? If you don't have any, you can create one using

LOAD RowNo() as UniqueIdentifier

Taking a step back, why are you trying to remove null sales from your Sum(Sale)? Sum(Sale) would never include any sales which are null? What is the goal here?

Re: Count Null by Set Analysis

Anonymous — Mon, 12 Dec 2016 11:34:32 GMT

Thanks Sunny,

Its sorted.

Farrukh