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

    set analysis

    Maria Damianova

      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
            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.

             

              • set analysis
                Maria Damianova

                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
                    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.

                     

                • set analysis

                  Try using

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

                  Nimish