topic Re: Set Analysis Help Please in QlikView

Set Analysis Help Please

Fri, 24 Oct 2014 06:00:10 GMT

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

Re: Set Analysis Help Please

jonathandienst — Fri, 24 Oct 2014 06:07:32 GMT

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

Re: Set Analysis Help Please

Fri, 24 Oct 2014 06:16:57 GMT

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.

Re: Set Analysis Help Please

kuba_michalik — Fri, 24 Oct 2014 06:26:54 GMT

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

Re: Set Analysis Help Please

Fri, 24 Oct 2014 06:29:39 GMT

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)

Re: Set Analysis Help Please

jyothish8807 — Fri, 24 Oct 2014 06:42:34 GMT

Hi John,

Try like this:

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

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

Regards

Re: Set Analysis Help Please

jagan — Fri, 24 Oct 2014 06:59:49 GMT

Hi John,

Try this expression

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

Hope this helps you.

Regards,

Jagan.

Re: Set Analysis Help Please

kuba_michalik — Fri, 24 Oct 2014 07:55:16 GMT

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.

Re: Set Analysis Help Please

Wed, 29 Oct 2014 07:16:56 GMT

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.

Re: Set Analysis Help Please

jagan — Wed, 29 Oct 2014 07:21:34 GMT

Hi,

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

Regards,

Jagan.