Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
cbaqir
Specialist II
Specialist II

AGGR Syntax with IF statement and FirstSortedValue Help

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))

7-7-2017 10-55-01 AM.jpg

TIA!

5 Replies
sunny_talwar

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))

cbaqir
Specialist II
Specialist II
Author

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))

sunny_talwar

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

cbaqir
Specialist II
Specialist II
Author

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))

cbaqir
Specialist II
Specialist II
Author

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.