13 Replies Latest reply: Jan 4, 2018 1:30 PM by Saravana Prabhu Paramaramaraj 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"


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