Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

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 (2)
1 Reply
Highlighted
Partner
Partner

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