Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
Maybe try using this:
=NullCount([Dealer Term Date])
Or Even this:
=Count( If( Isnull([Dealer Term Date]) = -1, [Dealer Term Date]))
Any luck?
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?
=NullCount([Dealer Term Date])
returned a value!!!
Now how do we incorporate this into our set analysis expression?
trying this now.
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])
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 "-"
The problem with that is the + (and) it needs to be (or).
Returned no value
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])
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?