Count Null by Set Analysis

[Anonymous]

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

[dineshm030]

Can you provide the sample QVW file?

[marcus_sommer]

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

[sunny_talwar · Accepted solution]

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)

[vinieme12]

Try

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

[Anonymous]

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

[sunny_talwar]

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?

[Anonymous]

Thanks Sunny,

Its sorted.

Farrukh