Set Analysis Specific Value or Null

[sqlpuzzle]

I am trying to get a count done using set analysis.

Count if

  Start_Date <= Max(SliderDate) OR Start_Date IS NULL

AND

End_Date >= Min(SliderDate) OR End_Date IS NULL.

I have the below expression so far. How can the null condition be included.

=Num(Count({< Start_Date = {'<=$(=Max(Date([Month Year])))'} ,End_Date = {'>=$(=Min(Date([Month Year])))'} > } Distinct ID),'###,##0')

Sample is attached.

Thanks

[sunny_talwar]

May be this

=Num(Count({<Start_Date = {'<=$(=Max(Date([Month Year])))'}, End_Date = {'>=$(=Min(Date([Month Year])))'}>+<ID = {"=Len(Trim([Start_Date])) = 0 or Len(Trim([End_Date])) = 0 "}> } Distinct ID),'###,##0')

[swuehl · Accepted solution]

Or maybe

=Count(

{< ID = e({<Start_Date = {'>$(=Max(Date([Month Year])))'}>+<End_Date = {'<$(=Min(Date([Month Year])))'} > }) >}

Distinct ID)

But if ID is on table record granularity, it might be easier to use an expression without set analysis.

edit:

like

=Count(Distinct If( Alt(Start_Date,0) <= $(#=Max([Month Year])) and   Alt(End_Date,1E9) >= $(#=Min([Month Year])),  ID))

[sqlpuzzle]

Can you explain what is happening in the expression. I want to understand your thinking better.

If I want to add one more dimension then can I use like

=Count(Distinct If( Alt(Start_Date,0) <= $(#=Max([Month Year])) and   Alt(End_Date,1E9) >= $(#=Min([Month Year])) and Type = 'A',  ID))

[swuehl]

Yes, this should work.

You can also create  a new / modify the date field in your script to replace the NULL with a value you can filter, like

[Sample]:

LOAD *, Alt(Start_Date, 0) as Start_Date_2;

LOAD ID,Date(Date#(Join_Date,'MMYYYY')) as Join_Date,Date(Date#(Start_Date,'MMYYYY')) as Start_Date,Date(Date#(End_Date,'MMYYYY')) as End_Date Inline

[ID,Join_Date,Start_Date,End_Date

1,'012017','012017','022017'

2,'012017','032017','042017'

3,'012017',,'032017'

4,'012017','012017','012017'

];

Then use Start_Date_2 with your original set expression.

[swuehl]

See also

NULL handling in QlikView

Excluding values in Set Analysis