Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

Avg and Aggr

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)

 

Labels (2)
3 Replies
Highlighted
Partner
Partner

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

 

 

Highlighted
Specialist
Specialist

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

Highlighted
Partner
Partner

Does every DEFECT_ID have a ERC_ASSIGNED_DATE?

 

Nate