topic Set Analysis 'is not null' in QlikView

Set Analysis 'is not null'

Thu, 28 Mar 2013 11:16:40 GMT

Hi,

I have a set analysis question. The existing, working expression I have is:

Sum({$<EVENT.ACTVTY_CDE={LEGALACT,HOLD,PYMNT,DEFER}>} BILLM_ME.TOTAL_AR)

Unfortunately this is returning a slightly high figure, so I'm trying to restrict this further by only including figures where the EVENT_DETAILS.CLOSE_DATE is not null. I know that there isn't really an 'is not null' in Qlikview so am trying to work around this by only selecting records where the close date is greater than 01 Jan 1900. Can anyone tell me what's wrong with the following expression? This currently returns £0.00

Sum({$<EVENT.ACTVTY_CDE={LEGALACT,HOLD,PYMNT,DEFER}, EVENT_DETAILS.CLOSE_DATE={">1900-01-01 00:00:00"}>} BILLM_ME.TOTAL_AR)

Thanks in advance, Gavin

Re: Set Analysis 'is not null'

perumal_41 — Thu, 28 Mar 2013 11:25:39 GMT

Try this Expression

Sum({$<EVENT.ACTVTY_CDE={LEGALACT,HOLD,PYMNT,DEFER}, EVENT_DETAILS.CLOSE_DATE={"> $(=Date(makedate(1900,1,1))"}>} BILLM_ME.TOTAL_AR)

Re: Set Analysis 'is not null'

Thu, 28 Mar 2013 11:38:17 GMT

Thanks, but unfortunately that doesn't return any data (now a dash '-' rather than 0)?

Gavin

Re: Set Analysis 'is not null'

kaushiknsolanki — Thu, 28 Mar 2013 11:48:15 GMT

Hi,

Try this.

Sum({$<EVENT.ACTVTY_CDE={LEGALACT,HOLD,PYMNT,DEFER}, EVENT_DETAILS.CLOSE_DATE={"$(=not isnull(EVENT_DETAILS.CLOSE_DATE))"}>} BILLM_ME.TOTAL_AR)

Regards,

Kaushik Solanki

Re: Set Analysis 'is not null'

perumal_41 — Thu, 28 Mar 2013 11:56:13 GMT

Try this Expression

Sum({$<EVENT.ACTVTY_CDE={LEGALACT,HOLD,PYMNT,DEFER}, EVENT_DETAILS.CLOSE_DATE={ ' >$(=Date(makedate(1900,1,1)))'}>} BILLM_ME.TOTAL_AR)

Re: Set Analysis 'is not null'

Thu, 28 Mar 2013 13:05:03 GMT

I'm afraid I've been a little hasty in posting. On further analysis it looks like events falling into the categories described will always have a close date set to null, so my original zero value was correct...Sorry to have wasted time, but I have learnt more about Set Analysis and null values from your answers and I think others also will, so answers marked as helpful (unfortunately only two 'helpful's allowed|). Thanks, Gavin.