Set Analysis with Aggr/Rank or Min?

[cbaqir]

I am trying to get a count of distinct defects created within the last 6 months.

I have a requirement to only account for the earliest DEFECT_STATUS_START_DATE since the defect could go through the New DEFECT_STATUS more than once. How do I account for this? Do I use min or Aggr/Rank somehow?

Count({$<DEFECT_STATUS_START_DATE={">=$(=Date(addmonths(monthend(today()),-6),'MM/DD/YYYY'))<=$(=Date(addmonths(monthend(today()),0),'MM/DD/YYYY'))"},DEFECT_STATUS={'New'}>} distinct DEFECT_ID)

TIA!

stalwar1

[ahaahaaha]

Hi,

May be like this

=Count({$<DEFECT_STATUS_START_DATE={">=$(=Date(addmonths(monthend(today()),-6),'MM/DD/YYYY'))"}*{"<=$(=Date(addmonths(monthend(today()),0),'MM/DD/YYYY'))"}*

{"=Aggr(Min(DEFECT_STATUS_START_DATE), DEFECT_ID)"}>} distinct DEFECT_ID)

[Miguel_Angel_Baeyens]

See

Count items with max date‌

and Finding matching dimension for min value‌

and Find out min and max Dates and show a value only from this row

[sunny_talwar · Accepted solution]

Try this

='Tickets Created (Expecting 736): ' &

Count({$<DEFECT_ID = {"=Min({<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) >= AddMonths(Today(),-6) and Min({<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) <= AddMonths(Today(), 0)"}, DEFECT_STATUS={'New'}>} DISTINCT DEFECT_ID)

Gives me 735 because the result is based on Today's date of 7/27/2017... but if you use this

='Tickets Created (Expecting 736): ' &

Count({$<DEFECT_ID = {"=Min({<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) >= AddMonths(Today()-1,-6) and Min({<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) <= AddMonths(Today()-1, 0)"}, DEFECT_STATUS={'New'}>} DISTINCT DEFECT_ID)

which is 7/26/2017, it gives 736

[cbaqir]

You are amazing!