1 Reply Latest reply on Aug 16, 2010 9:09 AM by philmodjunk

    Filtering Chart Data to be displayed



      Filtering Chart Data to be displayed



      I have a very basic chart in my database that displays portal records 'year' and 'costs'.   The 'Cost' values from 'Cost Field'  along the Vertical Axis and related 'year' values value from the 'Year Field' along the Horizontal Axis.    

      How do i get the chart to only display 'Year' and 'Cost' values going back say, ten years...?


        • 1. Re: Filtering Chart Data to be displayed

          I have a similar chart in one of my systems. In my case each row is a month of the year and each column is a year. Since the chart has 5 columns I get 5 years of summarized data broken down by month. Another drop down enables me to select a particular item or item group for the chart.

          What version of filemaker do you have? With filemaker 11, you can set up 10 portals side by side and use a filter on each portal that specifies a different year. With older versions, you'd set up 10 relationships and include the filtering logic in the relationship of each.

          In either case you can use a global field to record the most recent year you want to see and then use unstored calculation fields to specify the year for each column by subtracting different amounts from the specified year.

          In other words, You'd define something like gYear10 as a global number field and then define:

          cYear9 : gYear10 - 1
          cYear8 : gYear10 - 2
          cYear7 : gYear10 - 3

          and so forth.

          You can then use these fields either in the portal filters (filemaker 11, and you can just use the expressions instead of defining the fields, but you'll probably want them for column headers anyway.) or in the filtering relationships ( older versions ) to get your separate columns of data.