9 Replies Latest reply on Jan 27, 2017 12:13 PM by siplus

    Filter graph by last 6 months

    AndrewJudd

      hi all,

       

      Does anyone know how to filter a graph (pie or chart) to show only the last 6 months worth of data? Do I need to make a new table / relationship for the data source and sorting (at the moment just by date)

       

      Thanks

        • 1. Re: Filter graph by last 6 months
          philmodjunk

          It depends on the data source options that you select for your chart. If it's based on the current table and not related data, you just perform a find for all records dated 6 months from today or newer. If you chart data from a related table, you can set up a relationship where a match field controls what records are used as the source for your chart.

          And if you use ExecuteSQL to generate delimited data for your chart, the WHERE clause in the query can be set up to limit data by date in the same fashion.

          • 2. Re: Filter graph by last 6 months
            AndrewJudd

            thanks Phil, it is a chart of data from a related table. How do I use the relationship the filter the last 6 months? It is a primary and foreign key relationship. thanks

             

             

            Screen Shot 2017-01-27 at 17.41.53.png

            • 3. Re: Filter graph by last 6 months
              philmodjunk

              You'll need a relationship that also matches by date and uses an inequality, such as:

               

              YourLayoutTableOccurrence::PrimaryKey = Invoices|ByDate::ForeignKey AND

              YourLayoutTableOccurrence::gDate <= Invoices|ByDate::InvoiceDate

               

              Invoices|ByDate would be the name of a new table occurrence in your relationships graph and your chart would refer to data from this table occurrence instead of Invoices. (Click Invoices, then click the button with two plus signs...)

               

              gDate can be a global date field in whatever table is the basis for your chart layout. You can set it to whatever oldest date you want to limit the charted data to all Invoices of that date or more recent. you can update this field manually or via a script that uses the current date to calculate a date 6 months in the past.

               

              Edit note: had the wrong inequality operator...

              • 4. Re: Filter graph by last 6 months
                AndrewJudd

                thanks phil, secondly how do you group amounts into months? So for example the two January 2017s on the attached are in one portion. thanks

                 

                Screen Shot 2017-01-27 at 17.49.45.png

                • 5. Re: Filter graph by last 6 months
                  philmodjunk

                  Well, unless they added something in 15 you have conflicting requirements now. you can chart groups of data, but as far as I know, not from related records. If you base your chart layout on the related table and pull up a found set, you can sort on a single field to group your data and then use a summary field to provide the Y-series (wedge data).

                   

                  I do see a new option were you can sort the related records, but I don't think that will work to group the data and give you different values for a summary field defined in that table, but hey, I haven't tried it so maybe....

                  • 6. Re: Filter graph by last 6 months
                    AndrewJudd

                    Screen Shot 2017-01-27 at 19.03.25.png

                     

                    Thanks for all Phil, unfortunately I'm getting some funny results in the chart. I have set the gDate field to 01/01/2017 in the hope that the chart should display any invoices with a date equal to or made after this date. To test the chart I have opened a new window of the invoice table and experimenting with changing dates. An invoice dated 26th Jan shouws in the chart but 27th does not, then 28th it does? seems particular to some dates, if i choose the 11th for example it disappears but 12th it shows. Both fields are definitely date fields. Any ideas?

                    • 7. Re: Filter graph by last 6 months
                      AndrewJudd

                      ah i see the problem, if two invoices are the same date it is incorrectly grouping them into one segment of the pie chart.

                      • 8. Re: Filter graph by last 6 months
                        philmodjunk

                        Sounds like you need to specify a different sort order in order to get them to group differently.

                        • 9. Re: Filter graph by last 6 months
                          siplus

                          Doing 90% of my charting via Plugins that I won't mention again because you had plenty of it, I'm used to calculate my X data and my Y data separately.

                           

                          Here is a demo of how I deal with this kind of problems, see attachment.

                           

                          Feel free to change the gData date and to browse through clients when gData is set.

                           

                          You can omit single invoices from the portal by selecting (•) :  these will be excluded immediately from the drawn set and the pie chart will be redrawn.

                           

                          Feeding $$vars to a FMP generated graph makes it very fast, AFAIK. YMMV.