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: 
walimmalik
Contributor
Contributor

Aggr and Count when comparing dates

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?

1 Solution

Accepted Solutions
Gysbert_Wassenaar

Perhaps something like this will work:

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


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
Gysbert_Wassenaar

Perhaps something like this will work:

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


talk is cheap, supply exceeds demand
walimmalik
Contributor
Contributor
Author

Awesome, thank you works great!

walimmalik
Contributor
Contributor
Author

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?


talk is cheap, supply exceeds demand