topic Re: KPI based off of two columns in App Development

KPI based off of two columns

Evan0211 — Thu, 15 Jul 2021 15:41:13 GMT

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).

Re: KPI based off of two columns

Kushal_Chawda — Thu, 15 Jul 2021 16:30:30 GMT

@Evan0211 try below

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

Re: KPI based off of two columns

Evan0211 — Thu, 15 Jul 2021 16:35:23 GMT

That is returning 0, which is not correct.

Re: KPI based off of two columns

Kushal_Chawda — Thu, 15 Jul 2021 21:25:14 GMT

@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])

Re: KPI based off of two columns

Evan0211 — Fri, 16 Jul 2021 12:12:45 GMT

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?