Count based on another column

[yashcena]

Hi, I have the following columns in my data model:

JOB_NAME 

SLA_DEFINED_TIME

SLA_LOADDATE

SLA_LOADTIME

I have to create a KPI where I have to show the count of the days when the load time was greater than the SLA Defined time (i.e. when SLA was missed, in other words)

I have written the following expression (which isn't working):

count({<SLA_LoadTime ={">SLA_DEFINED_TIME"} >}SLA_LoadDate)

I'm trying to count the LoadDates where LoadTime >(greater than) SLA_DEFINED_TIME.

By default, at least one job will be selected in the app. So there will be ONE selection made for all the corresponding columns.

For example, when JOB01 is selected, then SLA_DEFINED_TIME becomes '1:00:00 AM'. But I think it is not being passed on to the expression that I have written.

when I hard code the SLA_DEFINED TIME, it works as expected

count({<SLA_LoadTime ={">3:00:00 AM"} >}SLA_LoadDate).

But I need to pass the selected job's SLA definition into that expression.

Any help would be appreciated!

Thanks,

YASH

[dplr-rn]

if you need to compare 2 columns you need to do set analysis on a unique identifier
e.g. below
count({<ID={"=SLA_LoadTime >SLA_DEFINED_TIME"} >}SLA_LoadDate)
or if both the fields are in the same table, you can create a flag in the script and use that in script
if(SLA_LoadTime >SLA_DEFINED_TIME,1,0) as Flag