Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
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')
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))
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))
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.