6 Replies Latest reply on May 20, 2009 9:25 AM by LaRetta_1

    ValueCount with multiple conditions

    trialuser1111

      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. 

        • 1. Re: ValueCount with multiple conditions
          philmodjunk
             If you can perfrom a find to find the records that match your criteria, you can then use get(foundcount) to count the number of matching records. There are other approaches as well.
          • 2. Re: ValueCount with multiple conditions
            LaRetta_1
              

            I'm still unclear on your structure.  Are 12/31/08: and 9/30/08: FIELDS within the Company Holdings table? 

             

            Your statements of  "and so forth back historically" and "where (Company Holdings::12/31/08 - Company Holdings::9/30/08) > 0" seems to clearly point to those dates as fields.  If so, you should consider changing your structure a bit because it is precisely these "variety of potential criteria combinations in endless combinations' which will cause you headache under your current start. 

             

            Any time you have multiple 'like fields' (in this example dates), it usually suggests that you should have a related table.  Once you have a proper structure, you can perform finds and generate summarized and grouped reports to perfectly fit your needs, no matter HOW your needs change.

             

            Can you clarify?

            • 3. Re: ValueCount with multiple conditions
              trialuser1111
                
              When I was setting up the data I felt like there was a better way to arrange it but I couldn't come up with anything.  So how should I arrange my data?  Right now, I have the "Company Data" table, related to "Company Holdings" by way of CompanyID, and I have "Company Holdings" related to "Analytics" by Ticker.   
              Should I create a table of holdings by DATE?  Maybe I should incorporate the holdings fields into the existing Company Data table?  I will need to be able to analyze ALL holdings across ALL companies.

              LaRetta wrote: 

               

              Any time you have multiple 'like fields' (in this example dates), it usually suggests that you should have a related table.  Once you have a proper structure, you can perform finds and generate summarized and grouped reports to perfectly fit your needs, no matter HOW your needs change.

               

              Can you clarify?


               

              • 4. Re: ValueCount with multiple conditions
                LaRetta_1
                  

                "Maybe I should incorporate the holdings fields into the existing Company Data table?"
                 
                Not a good idea.  Think of it this way, one Company can have multiple holdings, right?  Then that requires two different tables.  Can one particular Company Holding record have different date/amounts?  Since your example shows two dates (and two amounts) as fields, the answer is yes. 

                 

                At this point, you can either have multiple RECORDS for each date/amount or you may want to again split and have a Company Holding table AND a purchases table.  Keep in mind that I do not know your structure nor business so I am guessing even on what these dates/amounts represent.  Only you can make these decisions; I can only direct you to a vision of proper relational structure and repeat the mantra that any time you have like fields within same table (or many 'pieces' to one 'piece') it indicates it should be split.  The more you break apart, the more ways of retrieving and comparing the information.

                 

                At minimum, your Company Holdings table should have records like this:


                CompanyID – TickerID – Date –     Price
                345                ABC         6/21/09    45,000
                322                ZZZ         6/23/09    14,000

                 

                 "I will need to be able to analyze ALL holdings across ALL companies."

                 

                If all holdings are records then your analysis will be easy.

                • 5. Re: ValueCount with multiple conditions
                  trialuser1111
                    

                   

                  At minimum, your Company Holdings table should have records like this:


                  CompanyID – TickerID – Date –     Price
                  345                ABC         6/21/09    45,000
                  322                ZZZ         6/23/09    14,000

                   

                   

                  This is roughly what the table looks like now.  However, each company could have thousands of holdings, and those holdings may overlap from one company to another.  For example, CompanyID 345 could own 34,000 units of TickerID ABC, and CompanyID 322 could own 1200 units.  Other than calculations, the only fields in the Company Holdings table are:

                   

                  CompanyID (to identify the holding company)

                  TickerID  (to identify the holding)

                  Quarterly Dates (currently 16 fields)

                   

                  So to your point, each holding IS its own record, but I still cannot figure out a way to analyze the holdings based on multiple criteria. 

                   

                   

                   

                  • 6. Re: ValueCount with multiple conditions
                    LaRetta_1
                      

                    "This is roughly what the table looks like now.  However, each company could have thousands of holdings, and those holdings may overlap from one company to another.  "

                     

                    Then you may need a join table (many-to-many); I cannot say for sure.  You need to use relationships and grouped reports, from which to summarize your data.  I still question why you have quarters as dates (and particularly the field hardcoded with the year) because you are wanting to compare one field value to another.  And that still indicates another table.

                     

                    I would highly suggest hiring a developer for structural consulation.  Otherwise (or in addition), I would suggest that you bring one specific example to http://filemakerforum.com/ where you can post an sample of your existing structure (we cannot envision a complex structure with words alone).  Then ask how to achieve one specific result and work through it slowly.  You cannot post files here. Again, the problem with different quarters in different fields is that, to do a proper comparison, you will want to compare one quarter to another and that means comparing RECORDS.  And you can't compare two bits of information when they reside within the same record.

                     

                    This isn't something which can be determined (and properly advised) in the small confines of a forum post without understanding your business in a lot more detail.  There is no easy, set answers here, I'm afraid.