1 2 Previous Next 20 Replies Latest reply on Nov 15, 2015 11:49 AM by williamrollo

    Graph to show amount of invoices created in a year

      Title

      Graph to show amount of invoices created in a year

      Post

           Hi
           i have seen a similar post to this already on the forum but am still a little stuck.

           I want to be able to select a year from my drop down that will then go to my graph layout. On the graph, I want to have the year on the x axis, with jan to dec increments (column graph) and the quantity of invoices for that month on the y axis. I have attached below the script so far, but it doesnt work  - I have created dummy data for 2012 but this shows no graoh at all on the layout, for 2013 it does show the correct amount of invoices, (2 in feb 2012) but for the wrong month!

           I would love some advice! Of course I can supply further detail....

            

             
      •           Set Error Capture [ On ]
      •      
      •           Allow User Abort [ Off ]
      •      
      •           Freeze Window
      •      
      •           Commit Records/Requests [ Skip data entry validation; No dialog ]
      •      
      •           #Validate Dates
      •      
      •           If [ IsEmpty (PREF::gYear ) ]
      •      
      •           #Missing year
      •      
      •           Show Custom Dialog [ Title: "Warning"; Message: "Please select a year."; Default Button: “OK”, Commit: “Yes” ]
      •      
      •           Exit Script [ ]
      •      
      •           End If
      •      
      •           Close Window [ Name: "Sales by Item Detail"; Current file ]
      •      
      •           New Window [ Name: "Sales by Item Detail"; Height: 900; Width: 750; Top: 10; Left: 10; Style: Document; Close: “Yes”; Minimize: “Yes”; Maximize: “Yes”; Zoom Control Area: “Yes”; Resize: “Yes” ]
      •      
      •           Go to Layout [ “Graph Sales by Month” (Invoices) ]
      •      
      •           #Find by specified date range.
      •      
      •           Enter Find Mode [ ]
      •      
      •           Set Field [ Invoices::graph_year; PREF::gYear ]
      •      
      •           Perform Find [ ]
      •      
      •           If [ Get (FoundCount) = 0 ]
      •      
      •           #No records found
      •      
      •           Close Window [ Current Window ]
      •      
      •           Refresh Window
      •      
      •           Show Custom Dialog [ Title: "Message"; Message: "No orders found within the specified date range."; Default Button: “OK”, Commit: “No” ]
      •      
      •           Exit Script [ ]
      •      
      •           End If
      •      
      •           Show/Hide Toolbars [ Show ]
      •      
      •           Print Setup [ Orientation: Portrait; Paper size: 8.26" x 11.69" ] [ Restore; No dialog ]
      •      
      •           Sort Records [ Keep records in sorted order; Specified Sort Order: Invoices::graph_month; ascending ] [ Restore; No dialog ]
      •      
      •           View As [ View as List ]
      •      
      •           Go to Record/Request/Page [ First ]
      •      
      •           Refresh Window
      •      
      •           Enter Preview Mode
      •      
      •           Go to Record/Request/Page [ Last ]
      •      
      •           Set Field [ PREF::gPageNumber; Get (PageNumber) ]
      •      
      •           Go to Record/Request/Page [ First ]
      •      
      •           Pause/Resume Script [ Indefinitely ]
      •      
      •           Enter Browse Mode
      •      
      •           Close Window [ Current Window ]

      2013-02-13_02.45.05_pm.png

        • 1. Re: Graph to show amount of invoices created in a year
          philmodjunk

               What data source options did you select for your chart?

               Is there a field that contains a common value for all records of a given month?

               Here's an example:

               cMonth: Invoices::Date - Day ( INvoices::Date ) + 1

               That field computes a date for the first day of the month for any given date in the date field. If you sort by that field, the records will be grouped by month and in January to December order.

               MonthName ( cMonth ) can be used to provide the labels for your bars.

               And do you have a summary field in place to count the records after they are grouped by month?

               Have you specfiied that field to provide the Y values for your chart?

          • 2. Re: Graph to show amount of invoices created in a year

                 Hi Phil

                 I think your first question is one of the main issues!

                 x axis - Left ( MonthName ( INVOICE DETAILS::graph_month) ; 3 )

                 y axis - INVOICE DETAILS::Graph_fender_Count

                  

                 INVOICE DETAILS::graph_month  is a calculation of 'Month ( Invoice Date )'

                 INVOICE DETAILS::Graph_fender_Count  is a calc of  -

                 If(invoicedetails::Category = "Club Fender" and Invoice Number ; "1" ; ""). This is supposed to count a particular product (fender) that has been ordered as opposed to quoted.

                 all calcs set as numbers.

                 I have altered the graph, but now it is pretty well empty ! It does show entries on the x axis for the months that have a valid order/invoice, but no amount - I think this may be due to my count calc...

                  

            • 3. Re: Graph to show amount of invoices created in a year
              philmodjunk

                   For your graph, replace Graph_fender_Count with a summary field that computes the total of this field.

                   Make sure your data source settings are for found set; grouped data when sorted.

                   And then make sure to sort your records by graph_month.

              • 4. Re: Graph to show amount of invoices created in a year

                     It works now - thank you - I have two further questions.

                     If there is a result of zero in any of these months, can Iget the graph to show that month with a zero amount/no column?

                     And if I want to compare a year's results with another year, is there a way of showing this on the graph, like a shadow graph type thing? (probably 3d graoh I woudl have thought)

                     I know I would need to include two Year fields for this. but not sure how to go abiout the rest of it!

                      

                • 5. Re: Graph to show amount of invoices created in a year
                  philmodjunk
                       

                            If there is a result of zero in any of these months, can Iget the graph to show that month with a zero amount/no column?

                       Only if you have at least one record in that month in your found set. Since you are only counting "fenders", make sure that you do not perform a find only for invoice records in the Club Fender category and you should be OK unless you have a really bad sales month. If there are no invoices at of any kind for that month, you'd have to add a "dummy" record with the correct date to keep that month from being omitted from the graph.

                       And if I want to compare a year's results with another year, is there a way of showing this on the graph, like a shadow graph type thing? (probably 3d graoh I woudl have thought)

                       You can have side by side bars (two for each month) if you perform a find for both years, then sort your records first by month, then by year.

                  • 6. Re: Graph to show amount of invoices created in a year

                         Thanks Phil - I shall give those a go...

                    • 7. Re: Graph to show amount of invoices created in a year

                           Am a bit stuck with this side by side graph suggestion - for the side by side, do you mean two graphs next to each other on the same layout or a two barred column graph idea?

                           I am a little confused how to impliment a search for two years. I have created another global field 'year2' that the user can enter a  year from the same value list as the original year global. But how do I search for both years and then show the results of this in my graph(s)

                           Below is the current script, with my addition of year2. thank you

                             
                      •           Set Error Capture [ On ]
                      •      
                      •           Allow User Abort [ Off ]
                      •      
                      •           Freeze Window
                      •      
                      •           Commit Records/Requests [ Skip data entry validation; No dialog ]
                      •      
                      •           #Validate Dates
                      •      
                      •           If [ IsEmpty (PREF::gYear ) ]
                      •      
                      •           #Missing year
                      •      
                      •           Show Custom Dialog [ Title: "Warning"; Message: "Please select a year."; Default Button: “OK”, Commit: “Yes” ]
                      •      
                      •           Exit Script [ ]
                      •      
                      •           End If
                      •      
                      •           Close Window [ Name: "Sales by Item Detail"; Current file ]
                      •      
                      •           New Window [ Name: "Sales by Item Detail"; Height: 900; Width: 750; Top: 10; Left: 10; Style: Document; Close: “Yes”; Minimize: “Yes”; Maximize: “Yes”; Zoom Control Area: “Yes”; Resize: “Yes” ]
                      •      
                      •           Go to Layout [ “Graph Sales by Month” (INVOICE DETAILS) ]
                      •      
                      •           #Find by specified date range.
                      •      
                      •           Enter Find Mode [ ]
                      •      
                      •           Set Field [ INVOICE DETAILS::graph_year; PREF::gYear ]
                      •      
                      •           Set Field [ INVOICE DETAILS::graph_year; PREF::gYear2 ]
                      •      
                      •           Perform Find [ ]
                      •      
                      •           If [ Get (FoundCount) = 0 ]
                      •      
                      •           #No records found
                      •      
                      •           Close Window [ Current Window ]
                      •      
                      •           Refresh Window
                      •      
                      •           Show Custom Dialog [ Title: "Message"; Message: "No orders found within the specified date range."; Default Button: “OK”, Commit: “No” ]
                      •      
                      •           Exit Script [ ]
                      •      
                      •           End If
                      •      
                      •           Show/Hide Toolbars [ Show ]
                      •      
                      •           Print Setup [ Orientation: Portrait; Paper size: 8.26" x 11.69" ] [ Restore; No dialog ]
                      •      
                      •           Sort Records [ Keep records in sorted order; Specified Sort Order: INVOICE DETAILS::graph_month; ascending ] [ Restore; No dialog ]
                      •      
                      •           View As [ View as Form ]
                      •      
                      •           Go to Record/Request/Page [ First ]
                      •      
                      •           Refresh Window
                      •      
                      •           Enter Preview Mode
                      •      
                      •           Go to Record/Request/Page [ Last ]
                      •      
                      •           Set Field [ PREF::gPageNumber; Get (PageNumber) ]
                      •      
                      •           Go to Record/Request/Page [ First ]
                      •      
                      •           Pause/Resume Script [ Indefinitely ]
                      •      
                      •           Enter Browse Mode
                      •      
                      •           Close Window [ Current Window ]
                      • 8. Re: Graph to show amount of invoices created in a year
                        philmodjunk

                             I meant two bars for each month--one for each year in a single graph.

                             To find data for two years, you can use one of several approaches:

                             1) If the years are sequential, you can specify a range: 2012...2013 will find all invoice records with years of 2012 to 2013. Using global fields, that would look like:

                               
                        •           Set Field [ INVOICE DETAILS::graph_year; PREF::gYear & "..." & PREF::gYear2 ]

                             2) Or you can use two find requests:

                               
                        •           Set Field [ INVOICE DETAILS::graph_year; PREF::gYear ]
                                    New Record/Request
                        •      
                        •           Set Field [ INVOICE DETAILS::graph_year; PREF::gYear2 ]

                             With two find requests, you are finding all records with the value specified in gYear or the value specified in gYear2

                             3) perform two finds, but use Extend Found set instead of Perform Find on the second search so that it's results are combined with the results of the first search.

                        • 9. Re: Graph to show amount of invoices created in a year

                               Hi Phil

                               Thank you for that. I have tried as you suggested. I used option 2. See attachment for my graph results. I tried doing a sort by month and then year1 and then year 2, but it doesnt seem to sort the columns. I was expecting the first two to be Jan Jan, both reading 1 etc etc. Is there a way to make Year one columns red and the year 2 black? (for example)?

                               Thank you

                          • 10. Re: Graph to show amount of invoices created in a year
                            philmodjunk

                                 This doesn't look like its sorted correctly. There should be two fields listed in your sort records dialog:

                                 graph_month
                                 graph_year

                                 And they should be listed in that top to bottom order.

                            • 11. Re: Graph to show amount of invoices created in a year

                                   Thanks - I think I have found the problem - my cMonth calc field has the properties (number)  - Invoices::Date - Day ( Invoices::Date ) + 1 as suggested by you. The Invoices date field is in date format. When I look at the data in the two of them, I get a full date for invoices date, but a sting of numbers for cMonth...

                                   Should cMonth be set as a date as well or is it something else?

                                    

                              • 12. Re: Graph to show amount of invoices created in a year
                                philmodjunk

                                     Note that I haven't suggested using cMonth in the sort order for this. It works well for the original "one year" chart. But to get two years with parallel Month bars--one for each year, it won't work.

                                     the cMonth calculation will return a large number, the number of days from 12/31/0000 to the first of the month date it calculates unless you select Date instead of Number as the return type.

                                • 13. Re: Graph to show amount of invoices created in a year

                                       So I should change that cMonth to date format..? Done that but no change... the columns read feb jan sep apr jan. I think this is alphabetical from A-Z for month names and then year. Can I get the colmns to be Jan 2012 Jan 2013, feb2012 feb 2013 etc etc..?

                                  • 14. Re: Graph to show amount of invoices created in a year
                                    philmodjunk

                                         To repeat: you cannot use that field for the sort order that you need here.

                                         But sorting on cMonth will not produce an alphabetic order to the months. It will produce this order to your groups:

                                         Jan 2012, Feb 2012, Mar 2012...Jan 2013,  Feb 2013...

                                         which is why I have been trying to tell you not to use this field in your sort order, but since you aren't getting that order, something else is wrong here as well...

                                         The order you specify in your last post is the order you will get if you sort by the two fields that I put in a previous post in exactly the order shown.

                                    1 2 Previous Next