Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
There is only one aggregation function; so you can only put the set analysis specification after the =count( part...
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)
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