Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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
Thanks, Nate. I think the issue is how to get the syntax to recognize multiple ERC_ASSIGNED_DATE when there are multiple DEFECT_ID?
Does every DEFECT_ID have a ERC_ASSIGNED_DATE?
Nate