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!