Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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.
Those are actually values. I want to include any deals in the calculation where the flag for Won = 1, and the flag for RegSold = 1. Everything in the current set analysis has to be set, but I want the selection for Partner to come into the equation.
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.
The second option worked perfectly. Thank you