topic Re: Date Variables with Set Analysis in QlikView

Date Variables with Set Analysis

cbaqir — Thu, 17 Aug 2017 21:51:15 GMT

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)

Re: Date Variables with Set Analysis

Anonymous — Thu, 17 Aug 2017 22:03:15 GMT

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

cbaqir — Thu, 17 Aug 2017 22:08:30 GMT

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

NZFei — Thu, 17 Aug 2017 23:54:20 GMT

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

Re: Date Variables with Set Analysis

vishsaggi — Fri, 18 Aug 2017 00:40:01 GMT

Try this:

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

Re: Date Variables with Set Analysis

tresB — Fri, 18 Aug 2017 05:27:51 GMT

Try like:

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

=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

cbaqir — Fri, 18 Aug 2017 14:39:37 GMT

error

Re: Date Variables with Set Analysis

cbaqir — Fri, 18 Aug 2017 14:40:17 GMT

error in expression

Re: Date Variables with Set Analysis

cbaqir — Fri, 18 Aug 2017 14:41:13 GMT

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

vishsaggi — Fri, 18 Aug 2017 14:47:41 GMT

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

cbaqir — Fri, 18 Aug 2017 15:05:07 GMT

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

vishsaggi — Fri, 18 Aug 2017 15:39:17 GMT

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

cbaqir — Fri, 18 Aug 2017 15:49:15 GMT

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

cbaqir — Fri, 18 Aug 2017 15:51:00 GMT

See sample

Re: Date Variables with Set Analysis

cbaqir — Fri, 18 Aug 2017 15:58:46 GMT

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

vishsaggi — Fri, 18 Aug 2017 16:40:23 GMT

Check this attached?

Re: Date Variables with Set Analysis

cbaqir — Fri, 18 Aug 2017 16:42:03 GMT

Awesome, thanks!

Re: Date Variables with Set Analysis

antoniotiman — Fri, 18 Aug 2017 16:46:38 GMT

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

cbaqir — Fri, 18 Aug 2017 16:59:25 GMT

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)

Re: Date Variables with Set Analysis

antoniotiman — Fri, 18 Aug 2017 18:25:36 GMT

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