KPI based off of two columns

[Evan0211]

I am using a KPI that will look at two columns and I am confused on the syntax.

Column 1 - Initial Date

Column 2 - Completed Date

I need a KPI to say if the initial date is in the past (before today) and the completed date is empty, count it for the KPI.

I am new to Qlik and have the following:

=Count({$<[Initial Date] = {"<=$(=timestamp(now()))"} >} [List Title])

That will add up all of the initial dates that are in the past, but how do I condition it to also check the completed date column for an empty (or possibly null, I am not sure how the table handles null values).

[Kushal_Chawda]

@Evan0211  try below

Count({$<[Initial Date] = {"<=$(=timestamp(now()))"} ,[List Title]= E({<[Completed Date]={"*"}>})>} [List Title])

[Evan0211]

That is returning 0, which is not correct.

[Kushal_Chawda]

@Evan0211  try including primary key column of the table instead of  [List Title] like below

Count({$<[Initial Date] = {"<=$(=timestamp(now()))"} ,Primary_Key_column= E({<[Completed Date]={"*"}>})>} [List Title])

If you are unsure of primary key column then you can simply create it using rowno() like below

LOAD *,

          rowno() as Key

FROM table;

then your set analysis would look like below

Count({$<[Initial Date] = {"<=$(=timestamp(now()))"} ,Key= E({<[Completed Date]={"*"}>})>} [List Title])

[Evan0211]

I am unsure where that LOAD* script is located.  I loaded the table from a datastore.  I unfortunately do not have direct access to the data, the store works as a middle man.  To create the table, I pulled in the table chart and then added each dimension from the data tab.  Where can I see the load script that is generated?