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: 
mparker123
Creator
Creator

Average Between Dates

Hello Everyone,

I have two date fields a Project Start Date and a Project End Date. I have multiple projects I want to find the average duration of the projects by Project type and Business Unit. I attached a sample file to this discussion. The Straight table labeled "average by project type" and "average by business unit" In both tables there is a duration, the duration is taking the earliest start date and the latest due date of each project type or business unit and giving me the duration between them. I am trying get the average of each project type and business unit.

5 Replies
sunny_talwar

May be your want this

Avg(Aggr([Due Date] - [Start Date], [Project Type], Summary))

Avg(Aggr([Due Date] - [Start Date] , [Business Unit], Summary))

Capture.PNG

mparker123
Creator
Creator
Author

That works however i need to add an if statement or a set analysis to limited the expression.

If(Avg(Aggr(epic_due_date - epic_cf_start_date, epic_cf_pmo_business_unit, epic_summary) and DEPT='44840' and project_name <> 'PMO IMS Pipeline'))

This is the expression I am trying to use however it doesn't seem to be working

sunny_talwar

May be try this

Avg(Aggr(Only({<Project_name -= {'PMO IMS Pipeline'}, DEPT = {'44840'}>}[Due Date] - [Start Date], [Project Type]), Summary))

mparker123
Creator
Creator
Author

We are missing a parenthesis somewhere I tried to but it in at the end of the expression and at the end of the set analysis it doesn't seem to be working.

sunny_talwar

My bad, may be this

Avg(Aggr(Only({<Project_name -= {'PMO IMS Pipeline'}, DEPT = {'44840'}>}[Due Date] - [Start Date]), [Project Type], Summary))