topic Re: Set Analysis Specific Value or Null in QlikView

Set Analysis Specific Value or Null

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

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

Re: Set Analysis Specific Value or Null

sunny_talwar — Fri, 04 Aug 2017 18:50:19 GMT

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')

Re: Set Analysis Specific Value or Null

swuehl — Fri, 04 Aug 2017 18:59:48 GMT

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:

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

Re: Set Analysis Specific Value or Null

sqlpuzzle — Fri, 04 Aug 2017 19:44:46 GMT

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))

Re: Set Analysis Specific Value or Null

swuehl — Fri, 04 Aug 2017 19:51:58 GMT

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.

Re: Set Analysis Specific Value or Null

swuehl — Fri, 04 Aug 2017 19:58:22 GMT