topic Re: Duration from First Occurence in QlikView

Duration from First Occurence

cbaqir — Thu, 06 Jul 2017 18:06:04 GMT

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

Re: Duration from First Occurence

sunny_talwar — Thu, 06 Jul 2017 18:12:04 GMT

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

Re: Duration from First Occurence

cbaqir — Thu, 06 Jul 2017 19:23:55 GMT

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

Re: Duration from First Occurence

sunny_talwar — Thu, 06 Jul 2017 19:29:17 GMT

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

Re: Duration from First Occurence

Nicole-Smith — Thu, 06 Jul 2017 20:03:55 GMT

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

Re: Duration from First Occurence

cbaqir — Thu, 06 Jul 2017 20:26:30 GMT

Thanks!