Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

massimofavaro
New Contributor III

Count if finish date < start date

Hi,

I have a table with these two columns:
StartDate and FinishDate.

I'd like to create a measure that counts the number of rows that have a FinishDate < StartDate.

I tried several measures like

SUM({<FinishDate = {"<=$(=StartDate)"}>}             FinishDate)

or

SUM({<FinishDate = {"<=[StartDate]"}>}             FinishDate)

but it doesn't seem to work.

Can anyone please help me?
Many thanks!

1 Solution

Accepted Solutions
MVP
MVP

Re: Count if finish date < start date

Count({<UniqueID={"=FinishDate<=StartDate"}>} UniqueID)

The safest is always to have a field that is a unique ID for the rows on the left hand side to pick the correct rows.

Doing a Count() aggregation should be done instead of Sum unless you have a flag that is set to 1 for each of the rows.

6 Replies
MVP
MVP

Re: Count if finish date < start date

Count({<UniqueID={"=FinishDate<=StartDate"}>} UniqueID)

The safest is always to have a field that is a unique ID for the rows on the left hand side to pick the correct rows.

Doing a Count() aggregation should be done instead of Sum unless you have a flag that is set to 1 for each of the rows.

massimofavaro
New Contributor III

Re: Count if finish date < start date

So, if I haven't a unique ID, I should create a calculated flag field and use it in the set analysis, right?

MVP
MVP

Re: Count if finish date < start date

It is easy to create a Unique ID for the rows in a table in the load script.

Simply add it like this:

T1:

LOAD

   RowNo() AS T1.id,

   ....

;

massimofavaro
New Contributor III

Re: Count if finish date < start date

Thanks, yes, it's easy to add an ID.

I am not sure how

Count({<UniqueID={"=FinishDate<=StartDate"}>} UniqueID)

works, but it works.

I'll try to figure it out.

Many thanks!

MVP
MVP

Re: Count if finish date < start date

The "=FinishedDate<=StartDate" is a search. The search will check all rows in the table that contains UniqueID (the one at the end of the expression) and it will select from the field mentioned before the equals symbol. That is why the field in front of the equals symbol should be unique in the particular table or you MIGHT get the wrong selections.

Highlighted
massimofavaro
New Contributor III

Re: Count if finish date < start date

Ah, now I understood.
Thanks a lot, I didn't know about this possibility...!

Community Browser