Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cbaqir
Specialist II
Specialist II

Date Variables with Set Analysis

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)

30 Replies
cbaqir
Specialist II
Specialist II
Author

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
Champion III
Champion III

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
Specialist II
Specialist II
Author

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
Specialist II
Specialist II
Author

See sample

cbaqir
Specialist II
Specialist II
Author

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
Champion III
Champion III

Check this attached?

cbaqir
Specialist II
Specialist II
Author

Awesome, thanks!

antoniotiman
Master III
Master III

Another way

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

Regards,

Antonio

cbaqir
Specialist II
Specialist II
Author

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
Master III
Master III

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