Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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.
So, if I haven't a unique ID, I should create a calculated flag field and use it in the set analysis, right?
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,
....
;
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!
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.
Ah, now I understood.
Thanks a lot, I didn't know about this possibility...!