Set Analysis Expression with date range & null

[#1]

Hello,

What's wrong with my expression?

Count({<[Dealer Hire Date] = {'<=12/31/2015'},[Dealer Term Date] = {"$(= '>=' & '01/01/2015' & '<=' & '12/31/2015')" or null}>}[Student ID])

I am trying to count Student ID where their Dealer Hire Date is Less Than or Equal to 12/31/2015 AND they either have a Dealer Term Date in-between the dates of 01/01/2015 - 12/31/2015 OR they don't have a Dealer Term Date.

My KPI object keeps returning the value of 0. I have a feeling my problem fall within how I am writing in the expression 'OR NULL'.

Suggestions on how to write this?

[#2]

No. Maybe if we just try to figure out how to count the null fields first and then plug into the other expression.

I tried ...   

[Dealer Term Date] = {"$(=null())"}
>}

[Student ID])

and it returned the value of 0. But If we can get this to work then I feel we could get the larger expression to work. Thoughts?

I appreciate your help.

[miguelbraga]

Maybe try using this:

=NullCount([Dealer Term Date])

Or Even this:

=Count( If( Isnull([Dealer Term Date]) = -1, [Dealer Term Date]))

Any luck?

[miguelbraga]

Or even better to create a Flag in your script....

IF(LEN(TRIM([Dealer Term Date]))=0 or ISNULL([Dealer Term Date]), 1, 0) as Flag

Now Simply use below in any expression to count empty or null field

COUNT({<Flag = {1}>}[Dealer Term Date])

Then you could do this:

Count({<
[Dealer Hire Date] = {'<=12/31/2015'},

[Dealer Term Date] = {"$(= '>=' & '01/01/2015' & '<=' & '12/31/2015')")},

Flag = {1}
>}

[Student ID])

What happens?

[#5]

=NullCount([Dealer Term Date])

returned a value!!!

Now how do we incorporate this into our set analysis expression?

[#6]

trying this now.

[miguelbraga]

Maybe this Expression:

=

NullCount([Dealer Term Date])

+

Count({<
[Dealer Hire Date] = {'<=12/31/2015'},

[Dealer Term Date] = {"$(= '>=' & '01/01/2015' & '<=' & '12/31/2015')")}
>}

[Student ID])

[#8]

I added it to the script and it loaded fine. Then I updated the expression with...

Count({<
[Dealer Hire Date] = {'<=12/31/2015'},

[Dealer Term Date] = {"$(= '>=' & '01/01/2015' & '<=' & '12/31/2015')")},

Flag = {1}
>}

[Student ID])

and it didn't return any value "-"

[#9]

The problem with that is the + (and) it needs to be (or).

Returned no value

[sunny_talwar]

How about this:

Count({<[Dealer Hire Date] = {'<=12/31/2015'},[Dealer Term Date] = {"$(= '>=' & '01/01/2015' & '<=' & '12/31/2015')"}>+

           <[Dealer Hire Date] = {'<=12/31/2015'},[Dealer Term Date] -= {'*'}>}[Student ID])

[sunny_talwar]

What was the dimension when NullCount([Dealer Term Date]) returned a value or was this in a text box object it returned a value?

Also what is the chart dimension here?