2 Replies Latest reply on Jan 19, 2015 11:11 AM by NicholasWelander

    Charting by counts of specific text occurrences

    NicholasWelander

      Title

      Charting by counts of specific text occurrences

      Post

      I'm trying to create a column chart (and perhaps eventually a stacked column chart) from single table of data. The two fields in question are a date field and a field with a 4-option dropdown. I would like to plot the number of occurrences of each item in the dropdown by month, making four data series on the chart. E.g., I have 10 records in the month of January. 6 entries have option A in the field, 2 have option B and 2 have option C. The four data values in January would be 6, 2, 2, and 0.

        • 1. Re: Charting by counts of specific text occurrences
          philmodjunk

          Define a calculation field for each of your 4 options. I'll pretend that the literal values selected are "A", "B", "C" and "D". Substitute the actual values in these calculations:

          cOptionAFlag:

          DropDownField = "A"

          cOptionBFlag:

          DropDownField = "B"

          and so forth...

          Define summary fields that compute the total of cOptionAFlag, cOptionBFlag, and so forth....

          Sort your records by Month. You may need to define a calcualtion field to produce the correct value for this sorting such as:

          DateField - Day (DateField ) + 1 -- which returns the date for the first day of the month for all dates of the same month.

          With the summarized records data source option, Your various Y-series data can refer to the four summary fields that selectively compute these counts.

          PS. If you are fairly conversant in SQL, the ExecuteSQL function might be used to produce the same values without the need to add so many new fields to your table.

          • 2. Re: Charting by counts of specific text occurrences
            NicholasWelander

            I had a feeling it would come to that. Unfortunately, I have another 8 or so fields each with 5-10 options I would like to search by, resulting in over 100 extra fields. So, I think I better brush up on my SQL. Thank you for your response.