9 Replies Latest reply on Oct 21, 2016 8:40 AM by philmodjunk

    How to create a last 6 months expense chart

    logancornelius

      I want to create a chart that shows event expenses from the last 6 months. I would assume I could do a summary of the total expenses from every event in a month and then chart each month's total summary, but then I would have to update it every month to make it the last 6 months. How can I have it automatically generate the last 6 month's expenses?

        • 1. Re: How to create a last 6 months expense chart
          beverly

          reports (even ones used to Chart) rely on Finding and Sorting. you can script the process and then "trigger" it to run when you go to the layout with the chart. that makes it "automated". is that what you mean?

           

          beverly

          1 of 1 people found this helpful
          • 2. Re: How to create a last 6 months expense chart
            philmodjunk

            Just pointing out a niggling little detail.

             

            Charts rely on finding and sorting IF they are based on the current found set. There are two other charting options that do not (The data still has to be accessed and organized, but not thru specific "find" and "sort" operations on a found set).

             

            In your case, Beverly is exactly right for the most simple straight forward use of a chart to chart subtotals rather than individual record values.

            • 3. Re: How to create a last 6 months expense chart
              clayhendrix

              I agree. If you do not know how to do this, just ask. You'll get the help you need!

              • 4. Re: How to create a last 6 months expense chart
                logancornelius

                Thanks guys!

                 

                So each record has a portal for expenses that are connected to a join table.Screen Shot 2016-10-20 at 11.19.07 AM.png

                 

                Should I create a summary chart based on the join table for expenses that take place in each month? How should I go about that?

                • 5. Re: How to create a last 6 months expense chart
                  philmodjunk

                  Yes, the simple approach would base this on the join table.

                   

                  Do you know how to create summary reports?

                   

                  A chart that charts subtotals based on groups of records has a lot of similarities to that method:

                  a) The records have to be sorted in an order that groups the records--in your case groups them by month/year.

                  b) A sub summary part would use a summary field to show the sub total. In a chart, you'd refer to the summary field as your "Y-series" value

                   

                  There are differences:

                  a) Unless 15 added some capabilities, you can only sort on a single field for charted data. This can force you to add a calculation field for sorting that combines values from several fields if you would otherwise have to sort on multiple fields to get the needed groups. (Such as sorting on a year field and then sorting on a month field).

                  b) you'll need to be sure to select the option for summarized groups of data.

                   

                  There is a more difficult to set up option that you can also use: An ExecuteSQL query that returns a list of the subtotals that you'd use with the "delimited data" option. Not the simplest to set up, but it frees you from having to base your layout on the join table.

                  • 6. Re: How to create a last 6 months expense chart
                    logancornelius

                    Ok so I'm having some difficulty. I have the expenses in the portal summed, but then I want to sum the sums and it seems as if I can't do that.


                    Here is one view. This is on our events tables. It has the summed expenses for each event. I'd like to be able to sum the expense sums for all the events in a month then translate that to a table.

                    Screen Shot 2016-10-20 at 1.14.46 PM.png

                    It works better with my eventexpensesjoin table because I have a calculation of each individual expense then I can add those all up into a monthly sum of the expenses. I'm not trying to sum the sum. Screen Shot 2016-10-20 at 1.15.58 PM.png

                     

                    I've tried translating both of these reports on to a table and it doesn't seem to work so I'm a bit confused as to why not. (I understand more as to why the first instance doesn't work more than the second)

                     

                    I'm still struggling with the concept some, but I feel like I'm getting closer to grasping it. It helps being able to ask you guys.

                    • 7. Re: How to create a last 6 months expense chart
                      philmodjunk

                      Do you know how to create a summary report? Knowing how to do that makes doing the chart easier.

                       

                      Go to a layout based on the portal's table, not the layout with the portal.

                       

                      Sort by Event ID to group the records by month (forget about grouping by event you don't need to if you have field that lets you group them by month and year)

                       

                      You can now use a chart on this layout to chart the value of a summary field that totals your expenses.

                       

                      Here's a calculation that will return the same date (the first day of the month) for all dates in the same month that you can use to sort and group your records by month and year:

                       

                      Date - Day ( Date ) + 1

                      • 8. Re: How to create a last 6 months expense chart
                        beverly

                        semantics, Phil! Relationships can have a "sort" added to the "find", so I call a GTRR (go to related record) a "find", too. make it an ExecuteSQL() query and it can "find" and "sort". I've never seen a chart that didn't have something sorted.

                        • 9. Re: How to create a last 6 months expense chart
                          philmodjunk

                          That has nothing to do with my last post. If you read it again, you'll find that I am talking about sorting a found set, not a group of related records, not the results of an SQL query.

                           

                          Charting with a Y-series that uses summarized data in this fashion must be based on a found set, not delimited nor related data.

                           

                          The same result can be done with delmited data produced by ExecuteSQL using the "group by" clause, but that's not what I'm describing here.