3 Replies Latest reply on Aug 6, 2015 9:49 AM by Atkins

    Retrieving Unique Filed Values for a Chart



      Retrieving Unique Filed Values for a Chart


      Hello, I have a multi-layer question related to setting up a chart for some of my data.

      I have a table called Projects with a list of projects, and a field for each project called Active.  If Active = 1 the project is Active, if Active = 2 the project is completed.  I also have a field called Fiscal Year that holds a 4-digit year so I can sort by the number of active and completed projects in a given fiscal year.

      I am attempting to set up a chart that shows the Fiscal Years on the X-Axis and the number of Active and Completed projects on the Y-Axis.  I set up two calculation fields (Active_FY and Completed_FY) and two summary fields (SUM_Active_FY and SUM_Completed_FY) which are providing me the active and completed project counts for a given fiscal year.  That all appears to be working for my Y-Axis.

      Where I'm having trouble is creating a method to analyze the Fiscal Year field in my table to create a dynamic X-Axis of data.  For instance, right now I have one project in FY14 and many projects in FY15.  Soon I will have projects in FY16.  I don't want to have to go in and change the data set manually, I want a calculation to recognize that there are projects in FY16 and extend my X-Axis to 2016 in addition to 2015.  I'm assuming I'll need a calculation field that is global that "reads" all of the Fiscal Year fields in my table, but I don't know how to write that calculation or how to store it.  Should it be stored as repetitions or as a value list, etc?

      Is this even possible?  I've tried a custom function I found online called UniqueValues (https://www.briandunning.com/cf/596) and got no results.  I've also tried setting up a value list based on the field Fiscal Year and using ValueListItems( ) as my field calculation to at least show a representation of the values in that field but it shows no data either.  Any help is appreciated.  Thanks!

        • 1. Re: Retrieving Unique Filed Values for a Chart

          Actually, I keep experimenting with this and it seems like I might need a separate table for Fiscal Year in order to show a chart this way? It keeps trying to give me a data set for every record in Projects rather than aggregating based on Fiscal Year.  Is this the way to go...create a new table for Fiscal Year?  Does it make sense to create a new table just to format a chart?


          • 2. Re: Retrieving Unique Filed Values for a Chart

            I don't see the need for any of that if you are charting summarized data and sort your records by Fiscal Year. This is kind of like charting a summary report where each point on your chart is somewhat analogous to a sub summary layout part.

            This might work pretty well as a stacked bar chart.

            The only reason that I'd see for a special table is if there are fiscal years with zero projects and you want the x-axis scale to show every year.

            • 3. Re: Retrieving Unique Filed Values for a Chart

              Phil, thanks so much!  This was SO simple as I figured it should be.  It never occurred to me to sort the data.