topic Re: Set Analysis inside if condition in QlikView

Set Analysis inside if condition

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

Hi,

I have the following expression:

count(DISTINCT if(match (sts_application,'A','W','C','U'),ssn))

I want to use set analysis inside the if to get Week to date ssn.

The set analysis I have is:

{<new_create_date = {"$(='>=' & Date(Weekstart(Max(new_create_date)), 'M/D/YYYY') & '<=' & Date((Max(new_create_date)), 'M/D/YYYY'))"}>}.

Where shall I use the set analysis inside the If statement?

Thanks.

Re: Set Analysis inside if condition

Peter_Cammaert — Tue, 28 Mar 2017 11:07:36 GMT

There is only one aggregation function; so you can only put the set analysis specification after the =count( part...

Re: Set Analysis inside if condition

Peter_Cammaert — Tue, 28 Mar 2017 11:10:24 GMT

BTW you can drop the IF function call (causes very bad performance on large data sets) by including the match inside the set analysis spec. Like:

count({<sts_application = {'A','W','C','U'}, new_create_date = {"$(='>=' & Date(Weekstart(Max(new_create_date)), 'M/D/YYYY') & '<=' & Date((Max(new_create_date)), 'M/D/YYYY'))"}>}.DISTINCT ssn)

Re: Set Analysis inside if condition

Anonymous — Tue, 28 Mar 2017 11:14:54 GMT

Try this,

if(match (sts_application,'A','W','C','U'),Count({<{<new_create_date = {"$(='>=' & Date(Weekstart(Max(new_create_date)), 'M/D/YYYY') & '<=' & Date((Max(new_create_date)), 'M/D/YYYY'))"}>}.>}distinct SSN),'Unknown'

if the match condition satisfies it'll display value using setanalysis else it'll show Unknown value