Aggregate Sum if Statement

[#1]

I am trying to count deals that have a value of 50,000 or higher. Each deal may have multiple records, so I am trying to aggregate the deals and sum the value of those deals. I keep getting an error when I try to do this though. My code is below. If I take out the if statement, I can get a value, but it is not the right one. Any help would be appreciated. If you need more information, please just let me know.

Thanks

Naomi

count(distinct{$<[Fiscal Year] = {2011}, [Partner Geo] = {'AMER'}, [Program Name] = {'ACE'}, [First-in]= {1}, DateFlag = {'Submitted'}>}if((agg(sum([Sold Value SRP]),[Opportunity ID])>50000),[Opportunity ID])

[pover]

Try the following:

count(distinct{$<[Fiscal Year] = {2011}, [Partner Geo] = {'AMER'}, [Program Name] = {'ACE'}, [First-in]= {1}, DateFlag = {'Submitted'},[Opportunity ID]={"=sum([Sold Value SRP])>50000"}>}) [Opportunity ID])

Regards.

[Oleg_Troyansky]

Try this:

count(

{$<[Fiscal Year] = {2011}, [Partner Geo] = {'AMER'}, [Program Name] = {'ACE'}, [First-in]= {1}, DateFlag = {'Submitted'},

[Opportunity ID] = {"=sum([Sold Value SRP])> 50000"}

>}

distinct [Opportunity ID])

• Within Set Analysis, you can define a Set Modifier for the Opportunity ID to search those opportunities with the total value greater than 50000
• I believe DISTINCT goes after the SET expression

good luck!

[#4]

Thank you, both of the suggestions got me closer... (and they gave the same result), but they still aren't summing the entire deal over the multiple records. I am getting a count of the deals that have 50K on a single record, vs the sum of the records.

[pover]

How do you identify a Deal? Just replace that field in the set analysis to get the deals that have a total bigger than 50,000:

count(distinct{$<[Fiscal Year] = {2011}, [Partner Geo] = {'AMER'}, [Program Name] = {'ACE'}, [First-in]= {1}, DateFlag = {'Submitted'},[Deal ID]={"=sum([Sold Value SRP])>50000"}>}) [Deal ID])

or since I don't know exactly what number you looking for, the following would give you the number of deals that have at least one record over 50,000:

count(distinct{$<[Fiscal Year] = {2011}, [Partner Geo] = {'AMER'}, [Program Name] = {'ACE'}, [First-in]= {1}, DateFlag = {'Submitted'},[Opportunity ID]={"=sum([Sold Value SRP])>50000"}>}) [Deal ID])

etc.

Regards.

[#6]

The deal is identified by the Opportunity ID. There are just multiple rows for each opportunity ID (one for each product on the deal). So, the Opportunity ID is the unique identifier in our data.

[pover]

That formula should work then.

Maybe there's something I don't understand. Please, send a screenshot of what you are getting as a result now and what you are wanting as a result.

Regards.