Avg and Aggr

[cbaqir]

How do I calculate avg days when there could be multiple DEFECT_IDs for a month?

Dimension: =if(match(CUR_STATUS,'Closed', 'Deferred', 'Denied', 'Closed - Maintenance', 'Dismissed', 'Nursing Research & Analytics')>0,null(),dual( Month(ERC_ASSIGNED_DATE) & '-' & Year(date(ERC_ASSIGNED_DATE,'DD.MM.YY')), num(Year(ERC_ASSIGNED_DATE)) & num(Month(ERC_ASSIGNED_DATE), '00' )))

Expression: aggr(sum(today()-ERC_ASSIGNED_DATE),ERC_ASSIGNED_DATE,BUILD_RESOURCES_SUB) is double the number and still leaving blanks

aggr(avg(today()-ERC_ASSIGNED_DATE),ERC_ASSIGNED_DATE,BUILD_RESOURCES_SUB) leaves blanks when multiple tickets

Expression denominator: count(distinct DEFECT_ID)

[hallquist_nate]

Rather than calculating that nasty dimension, try calculating that dimension in your script.  The processing horsepower needed to calculate that dimension is gone, because you are just displaying a value.

In your expression, try using an Interval() function to calculate the time between date values.  

If those ideas don't work, or lead you to a solution, reply back and post some data in an app so we can take a deeper look.

Nate

[cbaqir]

Thanks, Nate. I think the issue is how to get the syntax to recognize multiple ERC_ASSIGNED_DATE when there are multiple DEFECT_ID?

[hallquist_nate]

Does every DEFECT_ID have a ERC_ASSIGNED_DATE?

Nate