Aggr and Count when comparing dates

[walimmalik]

Hi,

I need to calculate if a project was started on time.  To do this we compare the [Project planned finish date] to when the last activity was completed in the project (there are multiple activities).  Therefore, we have to find the max([activity finish dates]) and then compare that to the [project planned finish date].  I then want to count all those that are late and on time.

I am currently doing the following:

Count(Aggr(Max([activity finish dates]), [projects]) < [project planned finish date]).

This should give me the count of all on time projects, but the count is way too low (i have a data table set up showing all on time/late).

Any idea what is wrong?

[Gysbert_Wassenaar · Accepted solution]

Perhaps something like this will work:

count({<[ProjectId]={"=max([activity finish dates])< min([project planned finish date])"}>} distinct [ProjectID])

[walimmalik]

Awesome, thank you works great!

[walimmalik]

Could you explain what this set statement means: ({<[ProjectId]={"=max([activity finish dates])< min([project planned finish date])"}>}

Is it saying find the project ID where max{activity finish dates] < [min project planned finish date]

I didn't know you could use set statements like that.  Does the double = sign mean 'where' in this context?

[Gysbert_Wassenaar]

Is it saying find the project ID where max{activity finish dates] < [min project planned finish date]

Yes, exactly that. It's called set analysis. See these blog posts for more information:

A Primer on Set Analysis

Why is it called Set Analysis?