Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cbaqir
Specialist II
Specialist II

Duration from First Occurence

Select DEFECT_ID = 1073 and Statuses of New and Approved.

For this defect, the start date for new is 9/23/13 and there are two instances of Approved... one with a start date of 11/11/2013 and one with 11/25/13.

Right now the expression is showing me 63 days which is 11/25/13-9/23/13.

Avg(Aggr(if(max(DEFECT_STATUS_START_DATE)-min(DEFECT_STATUS_START_DATE)>1,(SUM(
Aggr(
Max(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))),FISCAL_YEAR, DEFECT_ID))

I only want the first occurrence of whichever Statuses (DEFECT_STATUS_MAP) are selected. In this example, I would want to see 49 days which is 11/11/2013-9/23/13.

TIA!

stalwar1

1 Solution

Accepted Solutions
Nicole-Smith

I believe this works how you want it to (it returns 49 for me, and the FirstSortedValue will take into consideration any status that is selected):

Avg(Aggr(if(max(DEFECT_STATUS_START_DATE)-min(DEFECT_STATUS_START_DATE)>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))),FISCAL_YEAR, DEFECT_ID))

View solution in original post

5 Replies
sunny_talwar

May be this

Avg(Aggr(if(max(DEFECT_STATUS_START_DATE)-min(DEFECT_STATUS_START_DATE)>1,(SUM(

  Aggr(

  Min({<DEFECT_STATUS = {'Approved'}>} 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))),FISCAL_YEAR, DEFECT_ID))

cbaqir
Specialist II
Specialist II
Author

I can't hardcode the status because it should apply to both selected statuses and the user can pick any 2.

sunny_talwar

I am not sure I am completely understanding what you need here?

Nicole-Smith

I believe this works how you want it to (it returns 49 for me, and the FirstSortedValue will take into consideration any status that is selected):

Avg(Aggr(if(max(DEFECT_STATUS_START_DATE)-min(DEFECT_STATUS_START_DATE)>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))),FISCAL_YEAR, DEFECT_ID))

View solution in original post

cbaqir
Specialist II
Specialist II
Author

Thanks!