topic Conditions to Set Analysis in QlikView

Conditions to Set Analysis

Tue, 17 May 2011 16:43:18 GMT

Hello Everyone,

I want to learn, how we can change the following if...else expression to a set analysis syntax.

sum(if(STAGE='Pending' AND CREATED_DATE<= Today() AND REVENUE_RECOGNITION_FLAG<>'No' AND OPP_REG_ARCHIVE<>'False' AND IS_PART_ACTIVE=1 AND DESIGN_REG_STATUS<>'Rejected', TOTAL_VALUE))

I would be grateful if someone could please answer it please,

Thanking you beforehand,

Bikash

Conditions to Set Analysis

erichshiino — Tue, 17 May 2011 18:09:31 GMT

Try this:

sum( {< STAGE={Pending}, CREATED_DATE= {"<= $( =Today())"}, REVENUE_RECOGNITION_FLAG-={No}, OPP_REG_ARCHIVE-={'False'} , IS_PART_ACTIVE={1}, DESIGN_REG_STATUS-={ Rejected} > } TOTAL_VALUE)

I guess the only trick part may be the CREATED_DATE. If it doesn't work, you should created a numeric field on the script to achieve this

The syntax would be something like:

LOAD

CREATED_DATE,

NUM(CREATED_DATE) AS NCREATED_DATE

... [ THE REST OF YOUR LOAD SCRIPT]

Then, on set analysis it would be:

sum( {< STAGE={Pending}, CREATED_DATE= {"<= $(=num(Today()))"}, REVENUE_RECOGNITION_FLAG-={No}, OPP_REG_ARCHIVE-={'False'} , IS_PART_ACTIVE={1}, DESIGN_REG_STATUS-={ Rejected} > } TOTAL_VALUE)

Hope it helps,

Erich

Conditions to Set Analysis

Tue, 17 May 2011 18:23:27 GMT

Thanks Erich, for your time.

I am giving a try to the code that you have suggested,

Regards,

Bikash