AnsweredAssumed Answered

Retrieving Unique Filed Values for a Chart

Question asked by Atkins on Aug 5, 2015
Latest reply on Aug 6, 2015 by Atkins


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 ( 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!