30 Replies Latest reply: Aug 20, 2017 4:31 PM by Fei Xu

# Date Variables with Set Analysis

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

• ###### Re: Date Variables with Set Analysis

What are you trying to count?. I can see that date variables was wrong... Try this and let me know if it works

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

• ###### Re: Date Variables with Set Analysis

trying to count Distinct DEFECT_ID... the expression says okay but the result says Error in set modifier function set

• ###### Re: Date Variables with Set Analysis

=count({\$<DETECT_DATE= {">=\$(vDefectStart)<=\$(vDefectEnd)"}, PRIMARY_DECISION_BODY -={'SMG Committee'}>}distinct DEFECT_ID)

• ###### Re: Date Variables with Set Analysis

This returns a number but it's lower (1027) than I expected (1331). I will look into it. Thanks!

• ###### Re: Date Variables with Set Analysis

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.

• ###### Re: Date Variables with Set Analysis

For this sort of things , you can achieve by several ways. Attached is another way and it is useful sometimes.

• ###### Re: Date Variables with Set Analysis

Try this:

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

• ###### Re: Date Variables with Set Analysis

error

• ###### Re: Date Variables with Set Analysis

Try like:

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

OR

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

Note: 'and' removed and changes made in red (corrections in brackets, comma..). If your variable producing right output in right format, it should work.

• ###### Re: Date Variables with Set Analysis

error in expression

• ###### Re: Date Variables with Set Analysis

Try this:

= count(

{<

DETECT_DATE= {"\$(= '>=' & \$(vDefectStart) & '>=' & \$(vDefectEnd))" }> * < e({1< PRIMARY_DECISION_BODY = {'SMG Committee'} >} )

>}

DISTINCT DEFECT_ID

)

IF doesn't work can you share your sample to look into please!!!

• ###### Re: Date Variables with Set Analysis

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.

• ###### Re: Date Variables with Set Analysis

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?

• ###### Re: Date Variables with Set Analysis

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'

• ###### Re: Date Variables with Set Analysis

See sample

• ###### Re: Date Variables with Set Analysis

Check this attached?

• ###### Re: Date Variables with Set Analysis

Awesome, thanks!

• ###### Re: Date Variables with Set Analysis

Another way

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

Regards,

Antonio

• ###### Re: Date Variables with Set Analysis

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 =

• ###### Re: Date Variables with Set Analysis

Maybe this

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

You can modify according your need.

Regards,

Antonio

• ###### Re: Date Variables with Set Analysis

DEFECT_STATUS={'New'} should not be needed anymore since we are identifying a New ticket by the DETEC_DATE and DEFECT_STATUS_START_DATE={"<=\$(=AddMonths(Today(),-6))"}>} is when it was rolling 6 months and should be DETECT_DATE is BEFORE the selected date range...

But, this is not returning anything:

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

• ###### Re: Date Variables with Set Analysis

There are no records with these conditions.

• ###### Re: Date Variables with Set Analysis

Then I explained it wrong. There should be 522. Here are the requirements:

• CUR_STATUS = Closed
• PRIMARY_DECISION_BODY NOT SMG Committee
• CLOSE_DATE between date variables of vDefectStart and vDefectEnd
• DETECT_DATE before vDefectStart

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

• ###### Re: Date Variables with Set Analysis

Try this?

=Count({\$< DEFECT_ID = P({< CUR_STATUS = {'Closed'},

CLOSE_DATE = {">=\$(vDefectStart)<=\$(vDefectEnd)"},

DETECT_DATE= {"<\$(vDefectStart)"}>})

*

E({1< PRIMARY_DECISION_BODY ={'SMG Committee'}>})>}DISTINCT DEFECT_ID)

• ###### Re: Date Variables with Set Analysis

Does not return anything.

• ###### Re: Date Variables with Set Analysis

Clear the selections from current selection box and try.

• ###### Re: Date Variables with Set Analysis

It doesn't seem to be responsive to my calendar variables?

• ###### Re: Date Variables with Set Analysis

Nor does it work in the table chart.

• ###### Re: Date Variables with Set Analysis

Shouldn't there be variable dates in the current selections? Why would it work when those are cleared?

• ###### Re: Date Variables with Set Analysis

It seems to work.