7 Replies Latest reply on Aug 3, 2011 9:53 AM by philmodjunk

    Incidents in field across the file in a chart...

    GeoffreyMartin

      Title

      Incidents in field across the file in a chart...

      Post

      Hello all,

      I have looked through the forum trying to answer this one, and I haven't found anything.  I have a field called "delay checklist".  I'm trying to get a pie chart of the percentage of instances of each item within that field over the found set.  What I get is a complete mess.  I've enlosed a copy of the data I think might help to help me below.

       

      Thanks again Forum folks.  This place has saved me a bunch of time, I appreciate all of your efforts.

      graph_problem.PNG

        • 1. Re: Incidents in field across the file in a chart...
          philmodjunk

          I think I see that this check box field is a checkbox group with multiple values that may be selected for each field.

          Summary fields that "count" a field (and the count function also) count the number of records where the field is not empty. It does not produce a count of how many times a field contains a specific value. If the field may only store a single value (such as with radio button fields), then this approach can work if you sort your records by this same field so that records with the same selected value are grouped together.

          Where this fails is when you have, say a value list of "Apple", "Pear", "Peach" and one record has "Apple" and "Peach" selected, another just "Pear" and a third as "Apple" and "Pear" selected. In that case, the approach you are using won't work as you can't get a count of the number of records with "Apple" selected, the number of records with "Peach" selected and also the number of records with "Pear" selected.

          Does this describe the issue for you?

          If so, please confirm and we can discuss an alternate approach that would allow you to extract counts for each individual selected value even when multiple values may be selected for a given record.

          • 2. Re: Incidents in field across the file in a chart...
            GeoffreyMartin

            That is indeed what I am trying to do.  I am trying to count the instances of each value (different reasons) in the delay checkbox across the records and form a percentage of the whole.  Thanks in advance for your help.

             

            -Geoff

            • 3. Re: Incidents in field across the file in a chart...
              philmodjunk

              Let's create a separate table where we have one record for each value in your checkbox field's value list. To avoid having to maintain two identical value lists, one in custom values and one here, you can update your value lists definition to refer to this table if you want.

              Relate this new table like this:

              Originaltable::CheckBoxField = ValueTable::Value

              Now define a calculation field in value Table as:

              Count ( OriginalTable::checkBoxField )

              and you can build your chart on a layout based on this new table.

              How it works:

              Checkbox groups store their multiple selected values as a list of values with each selected value separated by a return. When a field that stores a such a list of return separated values is used in a relationship, it matches to any one of the listed values.

              Using my simplified example. If a record in your original table has "Peach" and "Pear" selected, it will match to both the record in Value Table that has "Peach" stored in the value field and also to the record that has "pear" stored in the value field. (And thus, it get's counted twice, once for each value.)

              The biggest potential limitation to this approach is that you may not want to count these values for all the records in your original table, but rather a subset of the total. (All records for the month of june, for example.) If that's the case, you'd need to modify the relationship so that you can use global fields defined in the value table to filter which records are included in the count for each record in the value table.

              • 4. Re: Incidents in field across the file in a chart...
                GeoffreyMartin

                I'm with you all the way until we get to sorting the data for the month of June part.  I'm not sure what you are saying here.  I am trying to analysis by month and by YTD.  I'm confused with the process. 

                -Geoff

                 

                • 5. Re: Incidents in field across the file in a chart...
                  philmodjunk

                  Can you get this set up to work to chart all the data in the original table?

                  In order to limit this to records of a specific date range we'll need to create a more sophisticated relationship:

                  ValueTable::Value = Originaltable::CheckBoxField AND
                  ValueTable::gDate1 < OriginalTable::date AND
                  ValueTable::gDate2 > OrginalTable::date

                  gDate1 and gDate2 are global fields where you can specify the date range for your relationship and you can use them also in a calculation as part of the chart title as well.

                  If you select 6/1/2011 for gDate1 and 6/30/2011 for gDate2, your chart will show the results for the most recent month of June. (I'm using MM/DD/YYYY format here.)

                  1/1/2011 for gDate1 and get ( currentdate ) in gDate2 would produce a YTD report.

                  • 6. Re: Incidents in field across the file in a chart...
                    GeoffreyMartin

                    This worked well and the global date thing worked great.  Would I use this same structure to count the number of the same entries in a field across the database?  For instance, I have a field for Hospitals we transported to.  I would like to find out how many times we were at each hospital.  I've set this up and it works with the above method.  I was just curious if there is an easier way to do the same thing.

                    • 7. Re: Incidents in field across the file in a chart...
                      philmodjunk

                      It depends on what you need. Another approach is to set up a summary report with summary fields and sub summary parts on a layout based on the table where you enter this information. That can make this type of report possible:

                      Mercy General                 200
                      Good Samaritan                20
                      Doctor's Hospital              400
                      Emmanuel Medical Center 200

                      Grand Total: 820

                      You can also set it up to get monthly and/or yearly subtotals and you can use a variety of different finds to control what records are used in the report (Such as specifying what hospitals to include or what range of dates to use.)