nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Computed Column Date Comparison expression not working

rbguy

Hello,
I?m trying to create a computed column that produces a string status based upon the comparison of date field in the database. To get the STATUS, it needs to compare the DUE_DATE to the present day and determine if it is LATE, ON TIME, or EARLY
For example, if the present day is 2013-01-11, then the computed column should look like this:

DUE_DATE | STATUS

2012-12-12 | LATE
2012-01-11 | ON TIME
2014-01-01 | EARLY

Unfortunately with my computed column expression, no matter what value the DUE_DATE has, STATUS just remains blank.

Obviously I am doing something wrong, but I can?t figure out what. Here is my expression:

if (row["DUE_DATE"] < BirtDateTime.today())
"EARLY";
else
if(row["DUE_DATE"] == BirtDateTime.today())
"ON TIME";
else
if(row["DUE_DATE"] > BirtDateTime.today())
"LATE"

Is there a better way to compare dates or am I doing something else wrong?
Thanks!

Find more posts tagged with

Comments

kclark

Could you try changing "EARLY" to this.value = "EARLY" and so on? You could also and an else to make the status to "default" to see if the comparison is being ignored.

rbguy

kclark,
Thanks for the reply. I first tried changing all return values to this.value like you suggested. (ie this.value= "EARLY") but I got the same result with all blanks for STATUS. I then added an else at the end:

else
this.value = "DEFAULT";

However now, every value for status is now set to "DEFAULT" regardless of the date that is being compared. I must be doing something wrong with my comparison. Is there a better way to compare dates?

Thanks,
Roger

kclark

I created a computed column with this.value = BirtDateTime.today() to see the output. It looks like the format in your dataset is Mmm dd yyyy hh mm and your first post it looks like the format is yyyy mm dd so I think you should do something like this (I haven't tried it but it should work. 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
var convertedDate = Formatter.format(BirtDateTime.today(), "YYYY-MM-dd");

if (row["DUE_DATE"] < convertedDate)
"EARLY";
else
if(row["DUE_DATE"] == convertedDate)
"ON TIME";
else
if(row["DUE_DATE"] > convertedDate)
"LATE"</pre>
 
Edit: 
<pre class='_prettyXprint _lang-auto _linenums:0'>var convertedDate = Formatter.format(BirtDateTime.today(), "YYYY-MM-dd");
this.value = convertedDate;</pre>
 
Converted the date correctly in my computed column. Try the above code and let me know if it works.

bgbaird

Yet another method:

var intDelta = BirtDateTime.diffDay(row["DUE_DATE"] ,BirtDateTime.today());

if(intDelta>0){"EARLY";}
else if (intDelta==0){"ON TIME";}
else if (intDelta<0){"LATE";}
else {"ERROR";}

rbguy

Thanks kcclark!
That format change worked great. Everything is working like expected now. I really appreciate your help with this, it was driving me crazy.
(Out of curiosity, I tried bgbaird's method as well, it it seem to work good too.)
Anyway, thanks again to both of you!