4 Replies Latest reply: Mar 10, 2011 12:31 PM by Naomi James RSS

    Set Analysis that includes selections, and excludes others

    Naomi James

      I am trying to create a formula that will give me the total value of the sales for certain parameters. I want it to always give me the sum for the current year, a specific country, and a few other parameters, but I also want it to reduce it by the select partner. This is what I have so far, but it is giving me the sum for everyone, not limited to the selected partner, because of the {1} in the set analysis. It is important that the part I have in the set analysis is always there (regardless of selections), but how do I add in the selected partner?

       

       

      =

      sum({1<[Fiscal Year] = {2012}, [Partner Country] = {'United States','Canada'}, Won = {1}, RegSold = {1}, DateFlag = {'Closed'}>}[Calculated Sold Value SRP])





      Thanks for your help!

       

        • Set Analysis that includes selections, and excludes others

          Hello,

          In the part where you're using Won={1}, RegSold={1}, just change it for Won=, RegSold=, for indicate that doesn't matter selections on these fields.

          Tell me if it works please.

          • Set Analysis that includes selections, and excludes others
            John Witherspoon

             


            NaomiLyn wrote:it is giving me the sum for everyone, not limited to the selected partner, because of the {1} in the set analysis


            So... don't use {1} in the set analysis? What am I missing? Is this not what you want?

            sum({<[Fiscal Year] = {2012}, [Partner Country] = {'United States','Canada'}, Won = {1}, RegSold = {1}, DateFlag = {'Closed'}>}[Calculated Sold Value SRP])

            If you really want it to ignore everything BUT the selected partner, there are ways to do that. The simple way is like this:

            sum({1<[Partner]=P(),[Fiscal Year] = {2012}, [Partner Country] = {'United States','Canada'}, Won = {1}, RegSold = {1}, DateFlag = {'Closed'}>}[Calculated Sold Value SRP])

            But it's a bit TOO simple for many cases. It's looking at possible values, not selected values, and the possible values may be different than the selected values due to other selections. The other way is to check if selections have been made, if not search for "*" or nothing, and if so get the fields selections and insert them into the expression as the values to use. I can come up with the expression if necessary, but really, it seems like all you need to do is remove the 1.