Getting score with earliest due date

venori26
Tera Contributor

Hello Team, 

 

Below is my sample dataset.

 

I need to create an indicator 'past_due_days', which points to earliest due date. So from the data below, I need to get 25.

 

I have tried few options like creating an indicator with aggregate as minimum and field as Due date but it is giving me no scores. Idea is if I could create an indicator, which is minimum of due date, I could create a formula indicator that points to minimum date indicator

 

Can someone suggest how do I accomplish this task?

 

PLUGIN_IN_IDVULN_IDDUE DATEPAST_DUE_DAYS
XA01/08/2510
 B03/08/2520
 C05/08/2530
 D30/07/2525
2 REPLIES 2

Its_Azar
Tera Guru

Hi there @venori26 

try using a Scripted Indicator Source or formula indicator.

create an indicator with aggregate = Minimum on the Due Date. That gives you the earliest date.

Then in a formula indicator, calculate DATEDIFF(today, min_due_date) to get past_due_days.

If the OOTB aggregate isn’t giving scores, scripted indicator source is good: query the table, orderBy('due_date'), setLimit(1), and return that date → then compute days past due.

This way you’ll always get the earliest due date and can base your score on it.

 

hope this helps.

☑️ If this helped, please mark it as Helpful or Accept Solution so others can find the answer too.




Kind Regards,

Mohamed Azarudeen Z

Developer @ KPMG

 Microsoft MVP (AI Services), India

Thank you so much sir. As you mentioned, the first way you mentioned is giving me no scores. So i followed your second method. The past due calculation is already present in the table. May I request you to validate below script:

 

var val = "";
var gr = new GlideRecord('sn_vul_vulnerable_item');
gr.orderBy('ttr_target_date');
gr.setLimit(1);
gr.query();
if (gr.next()) {
val = gr.u_rpt_time_left_to_close;
}
val;