6 Replies Latest reply: Feb 5, 2010 12:57 AM by Oleg Troyansky

# set analysis

Hello,

I would like to divide the sum of [Email Promotions] by a count of [Individual Ids] but I want to exclude Individual Ids with Email Promotions=0 from this count.

The calculation below doesn't seem to work.

Any ideas what I am missing?

Thanks!

SUM([Number of Email Promotions])/count({\$<[Number of Email Promotions] >= {'1'}>}[Individual Id])

• ###### set analysis

Hello,

I think the problem is the symbol in red color in the formula:

SUM([Number of Email Promotions])/count({\$<[Number of Email Promotions] > = {'1'}>}[Individual Id])

Maybe you can use an "if" clause... something like If([Number of Email Promotions]>0...)

but I'm not sure if it's gonna give the same result.

Or maybe (if you want to use set analysis) you can try with {1-\$<[Number of Email Promotions] = {0}>} instead. But I'm not very good using set analysis.

Steve

• ###### set analysis

The problem is in the syntax of the Set Analysis.Try the following syntax:

SUM([Number of Email Promotions])/count({\$<[Number of Email Promotions] = {">0"}>}[Individual Id])

Notice three corrections:

1. Equal sign = instead of >= (the sign = means that your set is replacing user selections for that field)

2. Double Quotes instead of single quotes. Single quotes are used for textual values, while double quotes signify search.

3. The search criteria is ">0"

this Set Analysis expression should work.

• ###### set analysis

Oleg Hi!

how about nulls? I just noticed that my values are not 0 actually but nulls or blanks.

How could I exclude them from the count?

• ###### set analysis

Hi Maria!

good to see you here!

I think the condition ">0" will eliminate nulls or blanks as well - are you experiencing any problems with it?

Worst case - you could also move all those validations back into the script and create a flag that = 1 whn all the necessary conditions are met. Then you can simply summarize the flags.

• ###### set analysis

Try using

SUM([Number of Email Promotions])/count({\$<[Number of Email Promotions] = {'>=1'}>}[Individual Id])

Nimish

• ###### set analysis

I stand corrected, it should be double quotes as suggested by Oleg

nimish