6 Replies Latest reply: Apr 5, 2018 11:12 AM by Massimo Favaro

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.

Many thanks!

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

• 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?

• 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:

RowNo() AS T1.id,

....

;

• 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!

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

• Re: Count if finish date < start date

Ah, now I understood.