topic Re: Set Analysis: syntaxes, examples in QlikView

Set Analysis: syntaxes, examples

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Set Analysis: syntaxes, examples

petter — Sat, 24 Nov 2018 13:17:40 GMT

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.

Re: Set Analysis: syntaxes, examples

Anonymous — Mon, 26 Nov 2018 06:11:12 GMT

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.