topic Re: AGGR Syntax with IF statement and FirstSortedValue Help in QlikView

AGGR Syntax with IF statement and FirstSortedValue Help

cbaqir — Fri, 07 Jul 2017 17:56:14 GMT

I have a requirement to exclude any DEFECT_ID where:

Min({<DEFECT_STATUS_MAP-={'$(=FirstSortedValue(DEFECT_STATUS_MAP, DEFECT_STATUS_START_DATE))'}>}DEFECT_STATUS_START_DATE) - Min(DEFECT_STATUS_START_DATE)<1

but I still see many showing up with less than 1 day. What am I missing?

Select DEFECT STATUSES: New and Approved

My expression:

Avg(Aggr(if(max(DEFECT_STATUS_START_DATE)-min(DEFECT_STATUS_START_DATE)>1,(SUM(
Aggr(
Min({<DEFECT_STATUS_MAP-={'$(=FirstSortedValue(DEFECT_STATUS_MAP, DEFECT_STATUS_START_DATE))'}>}DEFECT_STATUS_START_DATE) - Min(DEFECT_STATUS_START_DATE)
,DEFECT_ID) )
/Count({$<DEFECT_ID = {"=Count(DEFECT_STATUS_MAP) = GetSelectedCount(DEFECT_STATUS_MAP)"}>}DISTINCT DEFECT_ID))),MONTH_YEAR, DEFECT_ID))

TIA!

Re: AGGR Syntax with IF statement and FirstSortedValue Help

sunny_talwar — Fri, 07 Jul 2017 18:05:04 GMT

May be you are looking for this

If(Sum(Aggr(

Min({<DEFECT_STATUS-={'$(=FirstSortedValue(DEFECT_STATUS, DEFECT_STATUS_START_DATE))'}>}DEFECT_STATUS_START_DATE) - Min(DEFECT_STATUS_START_DATE), DEFECT_ID))

/Count({$<DEFECT_ID = {"=Count(DEFECT_STATUS_MAP) = GetSelectedCount(DEFECT_STATUS_MAP)"}>}DISTINCT DEFECT_ID) > 1,

Sum(Aggr(

Min({<DEFECT_STATUS-={'$(=FirstSortedValue(DEFECT_STATUS, DEFECT_STATUS_START_DATE))'}>}DEFECT_STATUS_START_DATE) - Min(DEFECT_STATUS_START_DATE), DEFECT_ID))

/Count({$<DEFECT_ID = {"=Count(DEFECT_STATUS_MAP) = GetSelectedCount(DEFECT_STATUS_MAP)"}>}DISTINCT DEFECT_ID))

Re: AGGR Syntax with IF statement and FirstSortedValue Help

cbaqir — Fri, 07 Jul 2017 19:10:25 GMT

Thanks, Sunny. Let me dissect to make sure I understand...

I want to exclude those defects where the difference between the first occurrence of both selected statuses is less than 1, not where the average is less than 1, so, think I need to change the First part from this:

If(Sum(Aggr(

Min({<DEFECT_STATUS-={'$(=FirstSortedValue(DEFECT_STATUS, DEFECT_STATUS_START_DATE))'}>}DEFECT_STATUS_START_DATE) - Min(DEFECT_STATUS_START_DATE), DEFECT_ID))

/Count({$<DEFECT_ID = {"=Count(DEFECT_STATUS_MAP) = GetSelectedCount(DEFECT_STATUS_MAP)"}>}DISTINCT DEFECT_ID) > 1,

to this:

If(Sum(Aggr(

Min({<DEFECT_STATUS-={'$(=FirstSortedValue(DEFECT_STATUS, DEFECT_STATUS_START_DATE))'}>}DEFECT_STATUS_START_DATE) - Min(DEFECT_STATUS_START_DATE), DEFECT_ID))"}>}DISTINCT DEFECT_ID) > 1,

Does it still make sense?

If(Sum(Aggr(
Min({<DEFECT_STATUS_MAP-={'$(=FirstSortedValue(DEFECT_STATUS_MAP, DEFECT_STATUS_START_DATE))'}>}DEFECT_STATUS_START_DATE) - Min(DEFECT_STATUS_START_DATE), DEFECT_ID))
> 1,
Sum(Aggr(
Min({<DEFECT_STATUS_MAP-={'$(=FirstSortedValue(DEFECT_STATUS_MAP, DEFECT_STATUS_START_DATE))'}>}DEFECT_STATUS_START_DATE) - Min(DEFECT_STATUS_START_DATE), DEFECT_ID))
/Count({$<DEFECT_ID = {"=Count(DEFECT_STATUS_MAP) = GetSelectedCount(DEFECT_STATUS_MAP)"}>}DISTINCT DEFECT_ID))

Re: AGGR Syntax with IF statement and FirstSortedValue Help

sunny_talwar — Fri, 07 Jul 2017 19:13:00 GMT

If it gives you what you want, then syntax wise it looks good to me....

Re: AGGR Syntax with IF statement and FirstSortedValue Help

cbaqir — Fri, 07 Jul 2017 20:19:34 GMT

I think it's closer but the calculation isn't right. I am looking for the average. If I select Oct 2013 and New and Approved, I see two DEFECT_IDs: 1111 and 1102 with Avg days 12 and 27. I would expect the average days for the month to show as 20.5 but it's showing as 2 with the new expression:

If(Sum(Aggr(
Min({<DEFECT_STATUS_MAP-={'$(=FirstSortedValue(DEFECT_STATUS_MAP, DEFECT_STATUS_START_DATE))'}>}DEFECT_STATUS_START_DATE) - Min(DEFECT_STATUS_START_DATE), DEFECT_ID))
> 1,
Avg(Aggr(
Min({<DEFECT_STATUS_MAP-={'$(=FirstSortedValue(DEFECT_STATUS_MAP, DEFECT_STATUS_START_DATE))'}>}DEFECT_STATUS_START_DATE) - Min(DEFECT_STATUS_START_DATE), DEFECT_ID))
/Count({$<DEFECT_ID = {"=Count(DEFECT_STATUS_MAP) = GetSelectedCount(DEFECT_STATUS_MAP)"}>}DISTINCT DEFECT_ID))

Re: AGGR Syntax with IF statement and FirstSortedValue Help

cbaqir — Fri, 07 Jul 2017 20:35:39 GMT

I think the issue is the denominator.

Count({$<DEFECT_ID = {"=Count(DEFECT_STATUS_MAP) = GetSelectedCount(DEFECT_STATUS_MAP)"}>} DEFECT_ID)

It needs to only count those DEFECTS with >1 days between start dates.