AnsweredAssumed Answered

ValueCount with multiple conditions

Question asked by trialuser1111 on May 19, 2009
Latest reply on May 20, 2009 by LaRetta_1

Title

ValueCount with multiple conditions

Post

I am having trouble with a ValueCount function that I am trying to implement.  Here's the situation:

 

Just added a "Company Holdings" table, which lists the positions held by a Company.  In addition to the individual positions, there is a field "CompanyID" which establishes the relationship between a position and the Company.  There is also a field "Ticker" which establishes the relationship between the position and its various characteristics (maintained in another table "Analytics").  So a sample entry in the Company Holdings table would look like this:

 

CompanyID: 3504

Ticker: ABC

12/31/08: 18,000

9/30/08: 24,000

and so forth going back historically

 

What I need to be able to do is construct a ValueCount function that returns the position value if MULTIPLE conditions are met so that I can base various summary fields off the results.  For example, I would like to be able to sum the values of the positions where Company Holdings::CompanyID = 1234 and where Analytics::Group = "Industrial."  Or, I would like to be able to count the positions where Company Holdings::CompanyID = 3456 and where (Company Holdings::12/31/08 - Company Holdings::9/30/08) > 0

 

The variety of potential criteria combinations is virtually endless, so that poses another problem.  Any help at all would be greatly appreciated. 

Outcomes