how show count the Last 24 hr failed jobs in KPI object.

[rajneesh]

Hi Team,

I have table with 

JOBID,Datetimestamp,status.

Need to calculate how many jobs failed in last 24hr.But My timestamp is different.please help me how to achieve set expression with below format.

loadDateTime

2022-07-21T19:35:43Z

2022-06-16T01:19:19Z

2022-06-16T10:03:21Z

2022-06-16T15:14:18Z

2022-06-17T07:44:09Z

Thanks

Raj 

[justISO]

Hi, does function Timestamp(loadDateTime) shows correct to you?

if yes, maybe desired KPI could look like this:

count( if( Timestamp(loadDateTime) >= Timestamp(ReloadTime() -1) and status='fail', JOBID))

[rajneesh]

Not working 

[rajneesh]

Hi Please help me on this.

[Rishabh_Chaudhary]

Count({< status = {'F'} , loadDateTime = {"=Timestamp(loadDateTime,'YYYY-MM-DD HH.MM') >= Timestamp(now()-1)"}>}status)

[Mark_Little]

Hi @rajneesh 

Do something in script. First I would split out the Date and time. 

DATE(LEFT(loadDateTime,10)) as loadDate

TIME(LEFT(RIGHT(loadDateTime,9),8)) as loadTime

Then in a resident load or just normal load

IF(loadDate < Today() and loadTime < TIME(NOW()), 'Failed') AS Failed

Then front end you canm count(Failed)