Date Variables with Set Analysis

[cbaqir]

Can someone please help me get this in the right syntax?

=count({$<DETECT_DATE= {"<=$(vDefectStart) and >=$(vDefectEnd)"}*e( {1< PRIMARY_DECISION_BODY ={'SMG Committee'}) >}distinct DEFECT_ID)

[cbaqir]

When the date range is 2/16/16-8/16/16, I would expect 1331 as the expected output.

I am taking the logic from the graphs where the past 6 months is hard coded and changing it to use the calendar variables.

[vishsaggi]

Check this?

= Count({< DETECT_DATE = {">=$(vDefectStart)<=$(vDefectEnd)" } > + < DETECT_DATE = E({1< PRIMARY_DECISION_BODY = {'SMG Committee'}>}) >} DISTINCT DEFECT_ID)

I am getting 1300. Any way you can let us know how you reached 1331?

[cbaqir]

The above expression gives me 5054...

1331 comes directly from the source application.

Detect Dates >= 2/16/16  And <= 8/16/16 &PRIMARY_DECISION_BODY NOT 'SMG Committee'

[cbaqir]

See sample

[cbaqir]

Ok, I noticed that the ones that are not displaying in QV have a null PRIMARY_DECISION_BODY. How can we account for these? For example, 5530 & 5534.

Using count({$<DETECT_DATE= {">=$(vDefectStart)<=$(vDefectEnd)"} *e( {1< PRIMARY_DECISION_BODY ={'SMG Committee'}>})>}DISTINCT DEFECT_ID) just decreased my number.

I attached a sample.

[vishsaggi · Accepted solution]

Check this attached?

[cbaqir]

Awesome, thanks!

[antoniotiman]

Another way

=Count({$<DEFECT_ID=P({<DETECT_DATE={">=$(vDefectStart)<=$(vDefectEnd)"}>})*E({< PRIMARY_DECISION_BODY ={'SMG Committee'}>})>}DISTINCT DEFECT_ID)

Regards,

Antonio

[cbaqir]

How would I adjust this to show DEFECT_ID where CUR_STATUS = 'Closed' within the selected date range but that where DETECT_DATE is BEFORE the selected date range? I expect 405.

This is what I have when it's a rolling 6 months:

=Count({$<PRIMARY_DECISION_BODY-={'SMG Committee'},DEFECT_ID =
  {"=Min({<DEFECT_STATUS={'New'}>}DEFECT_STATUS_START_DATE) < AddMonths(Today(),-6) and Max({<CUR_STATUS={'Closed'}>}DEFECT_STATUS_START_DATE) >= AddMonths(Today(),-6)"}>} DISTINCT DEFECT_ID)

[antoniotiman]

Maybe this

=Count({$<PRIMARY_DECISION_BODY-={'SMG Committee'},DEFECT_STATUS={'New'},
DEFECT_ID=P({<CUR_STATUS={'Closed'},DETECT_DATE={">=$(vDefectStart)<=$(vDefectEnd)"}>})*
P({<DEFECT_STATUS_START_DATE={"<=$(=AddMonths(Today(),-6))"}>})>}
DISTINCT DEFECT_ID) 

You can modify according your need.

Regards,

Antonio