Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
petter
Partner - Champion III
Partner - Champion III

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.

View solution in original post

6 Replies
petter
Partner - Champion III
Partner - Champion III

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.

Anonymous
Not applicable
Author

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

petter
Partner - Champion III
Partner - Champion III

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,

   ....

;

Anonymous
Not applicable
Author

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!

petter
Partner - Champion III
Partner - Champion III

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.

Anonymous
Not applicable
Author

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