Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
yashcena
Contributor III
Contributor III

Count based on another column

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

Labels (1)
1 Reply
dplr-rn
Partner - Master III
Partner - Master III

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