10 Replies Latest reply: Dec 8, 2017 3:08 PM by Sunny Talwar RSS

    "If and" statement with null handling in script

    William Fu

      I have a flag which should trigger only when DtPrejuizo < DtReferencia, but not for rows where DtPrejuizo is null:

       

          If(Date(Date#([DtPrejuizo], 'YYYYMMDD')) < Date(Date#([DtReferencia], 'YYYYMMDD'))

          and Len(Trim([DtPrejuizo]))>0 , 1, 0) as FlagPrejuizo,

       

      I'm using FlagPrejuizo={'0'} in the "Current SALDO" measure; it is working as expected for the first four rows, since DtPrejuizo > DtReferencia.

       

      However the result is zero for the last two rows, where DtPrejuizo is null, when I want it to be the same as "SOMA SALDO"

      _dterefeffe.PNG

      I also tried a nested if, with the same results.