"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.

[pedrobergo]

Hi Willian,

I recommend you to use alt() function. It works like that...

• if the first expression returns a valid value, its use this value.
• If the first expression returns a not valid value, like a null, its use the value of the second expression.

So you can do the following:

if( alt(dtprejuizo, today()) < dtreferencia, 1, 0) as flag

Pedro

[sunny_talwar]

May be add FlagPrejuizo to this table and see if the last two rows are flagged 0, 1 or are they null?

[william_fu]

Flag is consistent with the measure:

[william_fu]

Pedro,

I tried your suggestion, but while the Alt() expression seems to be working, the flag still returns 1 for some reason

[pedrobergo]

Hi William,

Can you share the Current SALDO formula?

Pedro

[william_fu]

It's a very basic expression:

Sum({$<FlagPrejuizo={0}>} Saldo)

[sunny_talwar]

What if you approach the other way...

If(If(Len(Trim(Date(Date#([DtPrejuizo], 'YYYYMMDD')))) = 0, Today(), Date#([DtPrejuizo], 'YYYYMMDD')) >= Date#([DtReferencia], 'YYYYMMDD'), 0, 1) as FlagPrejuizo,

[pedrobergo]

I think that i understand you now. You need to set 0 to flag when dtprejuizo < dtreferencia OR dtprejuizo is null.

Try  this:

if( alt(dtprejuizo, (dtreferencia+1)) < dtreferencia, 1, 0) as flag

So, its compare dtreferencia´s next day with dtreferencia when dtprejuizo is null, then it wills return 0

Pedro

[william_fu]

Same thing unfortunately.

I tested the null statements by themselves in the script and they seem to be the problem:

Len(DtPrejuizo) as NumChar,

IsNull(DtPrejuizo) as NuloPrej,

However adding the measure in the table works as intended, as you can see by the columns in yellow