topic set analysis in QlikView

set analysis

Thu, 04 Feb 2010 23:11:07 GMT

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

Thu, 04 Feb 2010 23:39:17 GMT

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

Oleg_Troyansky — Fri, 05 Feb 2010 00:03:47 GMT

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

Fri, 05 Feb 2010 00:40:25 GMT

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.

set analysis

Fri, 05 Feb 2010 01:46:02 GMT

Try using

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

Nimish

set analysis

Fri, 05 Feb 2010 01:51:00 GMT

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

nimish

set analysis

Oleg_Troyansky — Fri, 05 Feb 2010 05:57:28 GMT

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.