set analysis

[#1]

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])

[#2]

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

[Oleg_Troyansky]

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.

[#4]

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?

Thanks for your help.

[#5]

Try using

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

Nimish

[#6]

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

nimish

[Oleg_Troyansky]

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.