topic Re: Average Between Dates in QlikView

Average Between Dates

mparker123 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Average Between Dates

sunny_talwar — Thu, 09 Nov 2017 17:06:01 GMT

May be your want this

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

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

Re: Average Between Dates

mparker123 — Thu, 09 Nov 2017 20:32:55 GMT

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

Re: Average Between Dates

sunny_talwar — Thu, 09 Nov 2017 20:46:22 GMT

May be try this

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

Re: Average Between Dates

mparker123 — Thu, 09 Nov 2017 21:17:54 GMT

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.

Re: Average Between Dates

sunny_talwar — Thu, 09 Nov 2017 23:27:58 GMT

My bad, may be this

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