topic Re: Avg and Aggr in QlikView

Avg and Aggr

cbaqir — Sat, 16 Nov 2024 02:28:28 GMT

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)

Re: Avg and Aggr

hallquist_nate — Tue, 17 Sep 2019 17:01:39 GMT

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

Re: Avg and Aggr

cbaqir — Wed, 18 Sep 2019 18:50:26 GMT

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

Re: Avg and Aggr

hallquist_nate — Thu, 19 Sep 2019 15:56:53 GMT

Does every DEFECT_ID have a ERC_ASSIGNED_DATE?

Nate