Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I only want to include rows in which max(DEFECT_STATUS_START_DATE)-min(DEFECT_STATUS_START_DATE)>1 for both the calculation of my Avg # days and the count of DEFECT_ID.
The Avg # days is using a denominator of the total count of DEFECT_IDs instead of just those in which max(DEFECT_STATUS_START_DATE)-min(DEFECT_STATUS_START_DATE)>1 . As you can see, if 10-13 is selected for the month and the Statuses selected are New and Approved, the Avg # days shows 8 but (27+12)/2 which should be 19.5. It's including those DEFECT_IDs where max(DEFECT_STATUS_START_DATE)-min(DEFECT_STATUS_START_DATE)<1.
How can I fix this?
if(max(DEFECT_STATUS_START_DATE)-min(DEFECT_STATUS_START_DATE)>1,$(vAvgDays))
vAvgDays = 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)
Same question on the # of defects. It shows 1 when there should be 2 in this case.
Thanks all!
Yes, and for the count to match the table and show 2.
Try this
Sum(Aggr(If(Max(DEFECT_STATUS_START_DATE)-Min(DEFECT_STATUS_START_DATE)>1,1,0), DEFECT_ID, MONTH_1))
Thanks as always Sunny!
How would I add the syntax to say specifically to return a count of DEFECT_STATUS_MAP = Closed DEFECT_ID to this?
Sum(Aggr(If(Max(DEFECT_STATUS_START_DATE)-Min(DEFECT_STATUS_START_DATE)>1,1,0), DEFECT_ID,FISCAL_YEAR))
May be this
Sum({<DEFECT_STATUS_MAP = {'Closed'}>}Aggr(If(Max(DEFECT_STATUS_START_DATE)-Min(DEFECT_STATUS_START_DATE)>1,1,0), DEFECT_ID,FISCAL_YEAR))
or this
Sum({<DEFECT_ID = {"=Count(DISTINCT {<DEFECT_STATUS_MAP = {'Closed'}>} DEFECT_ID) > 0"}>}Aggr(If(Max(DEFECT_STATUS_START_DATE)-Min(DEFECT_STATUS_START_DATE)>1,1,0), DEFECT_ID,FISCAL_YEAR))