topic Re: Aggr and Count when comparing dates in App Development

Aggr and Count when comparing dates

walimmalik — Wed, 29 Aug 2018 02:11:06 GMT

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?

Re: Aggr and Count when comparing dates

Gysbert_Wassenaar — Wed, 29 Aug 2018 15:26:38 GMT

Perhaps something like this will work:

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

Re: Aggr and Count when comparing dates

walimmalik — Wed, 29 Aug 2018 16:13:38 GMT

Awesome, thank you works great!

Re: Aggr and Count when comparing dates

walimmalik — Thu, 30 Aug 2018 16:35:59 GMT

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?

Re: Aggr and Count when comparing dates

Gysbert_Wassenaar — Fri, 31 Aug 2018 06:33:47 GMT

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?