topic GetFieldSelections and Max date in Set Analysis and AGGR in QlikView

GetFieldSelections and Max date in Set Analysis and AGGR

cbaqir — Wed, 19 Sep 2018 21:47:18 GMT

How do I calculate the avg days per DEFECT_ID for the selected statuses?

SUM(
Aggr(
Max(RES_STATUS_START_DATE)- Min(RES_STATUS_START_DATE)
,DEFECT_ID)
)
/Count({$<DEFECT_ID = {"=Count(RES_STATUS_AND_MODE) = GetSelectedCount(RES_STATUS_AND_MODE)"}>}DISTINCT DEFECT_ID)

Re: Days between two selected values

mdmukramali — Thu, 20 Sep 2018 05:54:52 GMT

Hi,

Can you attach sample data?

Thanks,

Mohammed Mukram

Re: Days between two selected values

cbaqir — Thu, 20 Sep 2018 14:44:03 GMT

Sample attached

If I used RES_STATUS (the one NOT in AND_MODE) I get two days.

I need the RES_STATUS_AND_MODE to show 2 days.

Re: GetFieldSelections and Max date in Set Analysis and AGGR

Anonymous — Thu, 20 Sep 2018 15:15:46 GMT

hi,

try this

avg(Aggr(Max(RES_STATUS_START_DATE)- Min(RES_STATUS_START_DATE),DEFECT_ID))

Re: GetFieldSelections and Max date in Set Analysis and AGGR

cbaqir — Thu, 20 Sep 2018 15:24:49 GMT

That gives me the same result. I think it needs to be filtered by GetFieldSelections(RES_STATUS_AND_MODE)

Re: GetFieldSelections and Max date in Set Analysis and AGGR

Anonymous — Thu, 20 Sep 2018 15:29:01 GMT

shouldn't need filter on RES_STATUS_AND_MODE as this selection is already considered

Re: GetFieldSelections and Max date in Set Analysis and AGGR

cbaqir — Thu, 20 Sep 2018 17:03:53 GMT

I need to get the Max start date from one status and the min start date from the other. In the example, it should be 2 days.