Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi Willian,
I recommend you to use alt() function. It works like that...
So you can do the following:
if( alt(dtprejuizo, today()) < dtreferencia, 1, 0) as flag
Pedro
May be add FlagPrejuizo to this table and see if the last two rows are flagged 0, 1 or are they null?
Flag is consistent with the measure:
Pedro,
I tried your suggestion, but while the Alt() expression seems to be working, the flag still returns 1 for some reason
Hi William,
Can you share the Current SALDO formula?
Pedro
It's a very basic expression:
Sum({$<FlagPrejuizo={0}>} Saldo)
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,
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
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