Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Wyz
New Contributor

Set Analysis: syntaxes, examples

Hi all,

 I have an expression in if condition which I am going to write that into Set analysis but the problem is in that between that Expression I have if Condition like----- if(voutstandingdate-JOIN_DATE >=331   in which voutstanding is a variable so please help me out that how we can write;

Full Expression is:-

=(
COUNT(DISTINCT(
IF(STATUS <> 'SOFT DELETED',
IF(ENROLMENT_ACTIVE = 'Y',
IF(JOIN_DATE <= voutstandingdate ,
IF([MIN DATE]<=voutstandingdate,
IF(ENROLMENTSTATUS_W='OPEN',
if(voutstandingdate-JOIN_DATE >=331 ,
if(voutstandingdate-JOIN_DATE<365,
IF([TOTAL COLLECTION]>0,
ENROLLMENT_ID))))))))))
+
COUNT(DISTINCT(
IF(STATUS <> 'SOFT DELETED',
IF(ENROLMENT_ACTIVE = 'Y',
IF(JOIN_DATE <= voutstandingdate ,
IF(ENROLMENTSTATUS_W <> 'OPEN',
if(voutstandingdate-JOIN_DATE>=331,
if(voutstandingdate-JOIN_DATE<365,
IF(STATUS_CHANGE_DATE>voutstandingdate,
ENROLLMENT_ID)))))))))
)

 

Please help me out ASAP

 Thanks in advance 

 

2 Replies
MVP
MVP

Re: Set Analysis: syntaxes, examples

Something quite similar to this I think:

RangeSum(

  Count(DISTINCT {<
    UniqueEnoughID={
    "=STATUS<>'SOFT DELETED' AND 
    ENROLMENT_ACTIVE='Y' AND
    JOIN_DATE<=$(voutstandingdate) AND
    MIN_DATE<=$(voutstandingdate) AND
    ENROLMENTSTATUS_W='OPEN' AND
    $(voutstandingdate)-JOIN_DATE>=331 AND
    $(voutstandingdate)-JOIN_DATE<365 AND
    TOTAL_COLLECTION>0"}
  >} ENROLLMENT_ID)
  ,
  Count(DISTINCT {<

    UniqueEnoughID={
    "=STATUS<>'SOFT DELETED' AND 
    ENROLMENT_ACTIVE='Y' AND
    JOIN_DATE<=$(voutstandingdate) AND
    ENROLMENTSTATUS_W<>'OPEN' AND
    $(voutstandingdate)-JOIN_DATE>=331 AND
    $(voutstandingdate)-JOIN_DATE<365 AND
    STATUS_CHANGE_DATE>$(voutstandingdate)"}
  >} ENROLLMENT_ID)
)

 

You will have to have a field that makes sure that none of the fields used in the boolean expression returns more than a single value. I have just called this field UniqueEnoughID.

 

Your date fields I presume are full dates with date formatting applied and not just numeric dates.

 

 

 

 

Wyz
New Contributor

Re: Set Analysis: syntaxes, examples

Thank you so much for your response,

so can you please tell me more about the UniqueEnoughID field actually I don't have that field and I didn't get it properly what you are trying to say, my main question is to how to write red colored if condition statement into set-analysis as I'm getting Error and how  you have written all set modifiers Expressions in one single "double Quotes" with AND condition and yes the  variable (voutstandingdate) is in date format.