Exclude Selection in expression without Set Analysis?

[Sean-CPT]

Hi Everyone,

So the problem I have is I've got the below statement working as is and I haven't been able to convert it using Set Analysis.

sum(If(GROUP_MONTH = REGISTRATION_MONTH and GROUP_MONTH >= vSixMonthsBack and Type = 'Joiners',
MEMBERS_JOINING))

variable: vSixMonthsBack = '201902'

I have a field COUNTRY that I basically want to ignore in the above expression. In Set Analysis I would've used {$<COUNTRY=>} .

Any advice on how to change this would be appreciated?

[tresesco · Accepted solution]

Try like:

sum({$<COUNTRY=>}  If(GROUP_MONTH = REGISTRATION_MONTH and GROUP_MONTH >= vSixMonthsBack and Type = 'Joiners',
MEMBERS_JOINING))

Also, if your variable definition has any aggregation function, try to use the same set analysis in that too like:

vSixMonthsBack= Max( {$<COUNTRY=>} DateField.....)

[Sean-CPT]

Hi Tresesco,

That worked precisely as I wanted it too, thanks for the help!