Set Analysis inside if condition

[_armoco_]

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.

[Peter_Cammaert]

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

[Peter_Cammaert]

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)

[Anonymous]

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