Set Analysis 'is not null'

[#1]

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

[perumal_41]

Hi

Try this Expression

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

[#3]

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

Gavin

[kaushiknsolanki]

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

[perumal_41]

Hi

Try this Expression

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

[#6]

  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.