Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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).
@Evan0211 try below
Count({$<[Initial Date] = {"<=$(=timestamp(now()))"} ,[List Title]= E({<[Completed Date]={"*"}>})>} [List Title])
That is returning 0, which is not correct.
@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])
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?