8 Replies Latest reply on Jul 7, 2016 1:58 PM by BrianClemens

    Help with YTD Sales Comparison Chart?

    BrianClemens

      Basically I am trying to replicate a chart I do manually in Keynote.  The chart looks at year to date sales by month and uses a column chart to compare that to last year.  I have a field for sold date and a calculated field for sold year.  I'm guessing it would be a sub-summary by month and would have two Y-axis data sets?

      Screen Shot 2016-07-06 at 4.57.01 P.png

        • 1. Re: Help with YTD Sales Comparison Chart?
          Mike_Mitchell

          For something like this, I typically set up a separate table that holds the values, which are updated via script whenever you close the books for that month. This way, the figures are indexable and the reporting (including charts) will perform very fast, as opposed to waiting around on summary fields (or ExecuteSQL results).

           

          HTH

           

          Mike

          • 2. Re: Help with YTD Sales Comparison Chart?
            karina

            Hi,

             

            I created a sample file for you.

            • 3. Re: Help with YTD Sales Comparison Chart?
              JulianJohnson

              I've built one of these this week. I use a scheduled script on the server to calculate:

               

              Sales this month

              Sales this year to date

              Sales last year to the same date

              Growth (Sales this year to date - Sales last year to same date)

              Sales for the whole of last year

              Timestamp when data was last calculated

               

              It uses a preference value for the financial year end then a looping script to set the query dates and get each of the values as variables.

               

              Finally it sets three variables containing, separated by carriage return, month name, Last year's sales for month and this year's sales for month. It then goes to the table underlying our home layout and puts the variables into fields.

               

              When the user logs in, they see a dashboard view that shows the sales figures and a graph showing the delimited sales data to compare months. It's a graph with month name on the x-axis and two y-axis series showing last year's and this year's figures. We have a load of other information on the dashboard showing quotes pipeline by month, stock needing reordering etc, all calculated by the scheduled script.

               

              The scheduled scrip runs every hour but if a user needs to update it between these I have a refresh button that lets them run the script on the server.

               

              I hope this helps....

              • 4. Re: Help with YTD Sales Comparison Chart?
                BrianClemens

                This is sort of unique in that it is unit sales and not accounting based.  So we never really close the month...This report is based off of retail registrations.  So it is July, but I might get one today that was sold in May.  When we enter this into the system, the sold date gets put in (not the registration date).  So it needs to be live.So if I run the report tomorrow, the May numbers will pick up the latest registration that we get.

                • 5. Re: Help with YTD Sales Comparison Chart?
                  Mike_Mitchell

                  Write a script that updates the relevant fields in the totals table whenever anything happens that affects the total. Use Script Triggers or embed the actions into the script that performs the update.

                  • 6. Re: Help with YTD Sales Comparison Chart?
                    BrianClemens

                    I can sort of get this to work.

                     

                    Right now if I create a find for units sold in 2015 and 2016, I get the record set that I need.  I sort for Month sold, then year sold and have a sub-summary that counts how many records are in there.  That gives me a layout that shows Jan 2015 totals, then Jan 2016 totals.  When I chart that, it just gives me totals per month. (combing 2015 and 2016)

                     

                    Is there a way to specify in the chart calculation on the Y series data, to pull only 2016?

                    • 7. Re: Help with YTD Sales Comparison Chart?
                      Mike_Mitchell

                      You need two data series. You can either do that by doing what we're telling you to do - store the records by month in a table - or by creating two sets of return delimited values in global variables and use that.

                      • 8. Re: Help with YTD Sales Comparison Chart?
                        BrianClemens

                        Sorry, I haven't had to work on this database (other than trivial stuff) for about 2 years...so my somewhat limited skills are worse yet.  I'm going to do a little re-educating and I'll check back when I am able to test these options out.