Set Analysis Help Please

[#1]

Hi Just getting started with set analysis and getting stuck on using built in functions can someone please set me in the right direction?

I want to Count the number of records where

[EventActionDueDate] < today() and [EventActionCompleted] is blank or null.

I have tried several variations of this to solve the fist half of the expression but no luck yet.

Count ({$<[EventActionDueDate] <= {"$(=today())"}>} [EventActionTranID] )

[jonathandienst]

Hi

Assuming the date format of EventActionDueDate is the default for your system (ie the same as that returned by Today()), then

Count({$<[EventActionDueDate] = {"<=$(=Today())"}>} [EventActionTranID])

should do it.

HTH

Jonathan

[#3]

Thanks Jonathan

That does indeed return a value but it only solves the first half of the problem [EventActionDueDate] < today()

Unfortunately I have no idea how to go about solving the second half

[EventActionDueDate] < today() and [EventActionCompletedDate] is blank or null.

[kuba_michalik]

That's uniquely ill-suited for set analysis, because as far as I know, there is no surefire way to get a set of all records where a field is null (Field={} does not work, if I'm wrong, I'll be glad to be shown wrong! ). If you can make sure (during the data load) that the missing date is always an empty string, but never null, then it is reasonably simple:

Count({$<[EventActionDueDate] = {"<=$(=Today())"}, [EventActionCompletedDate={''}]>} [EventActionTranID])

[#5]

My suggestions is that you create a new field in the script where you assaign blank or null values a date, ie 20991231. But you can test and se if Count({<[EventActionDueDate] = {"*"} >} EventActionTranID excludes blank o null? 

In that case maybe count({< EventActionDueDate = {"*"} >*<  and Jonathans expression  >} EventActionTranID)

[jyothish8807]

Hi John,

Try like this:

Count({$<[EventActionDueDate] = {"<=$(=Today())"},[EventActionCompletedDate]={''}>} [EventActionTranID])

or

Count({$<[EventActionDueDate] = {"<=$(=Today())"},[EventActionCompletedDate]-={*}>} [EventActionTranID])

Regards

KC

[jagan]

Hi John,

Try this expression

Count({$<[EventActionDueDate] = ([EventActionDueDate] -  {'*'}) + {"<=$(=Today())"}>} [EventActionTranID])

Hope this helps you.

Regards,

Jagan.

[kuba_michalik]

I tested it a bit, and I think this should work without changing nulls to a value:

Count({$<[EventActionDueDate] = {"<=$(=Today())"}>*-$<[EventActionCompletedDate]={"*"}>} [EventActionTranID])

Explanation: You can't define a set for all records where field is null, the same as you can't select a null value. So things like this: [EventActionCompletedDate]=[EventActionCompletedDate]-{"*"} won't work. But you can define a set of all records where a field is not null (that's $<[EventActionCompletedDate]={"*"}>), then get its complement, so, a set of records where a field will be null (-$<[EventActionCompletedDate]={"*"}>), and finally, get an intersection of this set with a set of records which satisfy your condition for EventActionDueDate.

[#9]

Hi Thanks for all the suggestions. Just to close this thread off the final solution was to add some conditional expressions in the load script to create an [EventActionStatus] field. See bellow.

If(NOT ISNULL(EventActionCompletionDate),

If(EventActionCompletionDate > EventActionDueDate, 'Late', 'Ontime'),

If(EventActionDueDate < today(), 'Overdue', 'Outstanding')) as EventActionStatus,

If(ISNULL(EventActionCompletionDate) AND EventActionDueDate < Today(), Round(Today() - EventActionDueDate)) as OverdueDays,

This had the effect of flagging Null fields during load thus removing the need for the set analysis to handle potential Null's.

[jagan]

Hi,

Getting the flags for nulls in script is better approach then handling it in front end using set analysis.

Regards,

Jagan.