4 Replies Latest reply on Jan 9, 2012 2:28 AM by JamesMahoney

    Calculating within a Value List



      Calculating within a Value List


      If this has been answered before, sorry - just refer me to the answer! Or if this is the wrong forum, blame a novice!

      I have a small database in which I am collecting information about job advertisements in the professional communication area.

      One VALUE LIST, "Profession" has the following choices: PR, Sales, MarCom, Advertising, Marketing. Another value list, "Industry", has these choices: Consultancy, Government, Industry, Not-for-Profit. The value lists use the drop down function.

      I can, of course, sort the DB using the find function, but is it possible to write calculation fields that will enable me to automatically sum each choice in each value list so that I can produce some comparative stats automatically without having to manually sort and transfer data to Excel.

      The kind of result I am after ultimately is: What % of PR jobs were in Government? Or using other vakue lists, what % of Advertising jobs were described in job ads as "strategic"?

      Help! No doubt the answer is blindingly obvious, but I can't work it out!




        • 1. Re: Calculating within a Value List

          I think you were thinking you were restricted to finding the first value, counting the records, finding the second value, counting the records, etc.  You could simply have a summary field which is the count of the field.  Then find all the records you are interested in analysing, sort them by that value, and include that summary value in a sub-summary part.  You don't have to include any body part if you don't want to.  You could show the grand total count at the bottom of the listing.  You could calculate the fraction by including a calculation in the sub-summary part, say:

          GetSummary ( SummaryCountOfThatValue ; ThatValueField ) / Get ( FoundCount )

          • 2. Re: Calculating within a Value List

            If each field formatted with one or the other of your two value lists permits only one value selection....

            If you want these percentages for either all the records in your table or for a found set you pull up....

            You can do the following:

            define constOne as a calculation field, returning the constant value: 1.

            Define a summary field as fraction of total of constOne.

            Create a layout to be used in List View with a sub summary part "when sorted by" your first value list formatted field. Create a second sub summary part "when sorted by" your second value list formatted field.

            Put your summary field in one or both of these sub summary parts.

            You can then perform a find to pull up the records you want for your report, then sort them by these two fields, to see your percentages. (Format summary field as a percent.)

            If you do not want to see the individual records listed in the body, only the percentages, remove the body part from your layout.

            • 3. Re: Calculating within a Value List

              Thanks so much for this. I appreciate the help. 

              • 4. Re: Calculating within a Value List

                Both answers are a great help. If I can't work it from here, I'll re-design the thing without value lists!


                Thanks again.