10 Replies Latest reply on Dec 16, 2015 8:20 AM by RishiKhanna

    Graph Help

    RishiKhanna

      I am trying to make a graph based on a specific company's sales,

       

      My table has the CompanyId, CurrentYear, MonthId, CurrentYearSales, PriorYearSales.

       

      I was thinking of searching through the table in order to get the graph to populate

       

      for example:

      Searching for months from January- May and for companyId 3 would be searching in my monthId field for 1...5 and my CompanyId would be 3

      I tested this and it works,

      however i am having trouble making the 1 and 5 into variables and putting data in them

       

      What is a good way to set up a dropdown of months for this to work

       

      Thanks,

      Rishi Khanna

        • 1. Re: Graph Help
          mikebeargie

          Your setup doesn't account for year so I'm assuming you're going by current year.

           

          All you should need is a couple of number fields, set to global storage, with a drop down of values from 1-12.

           

          Then you can get it into a variable by using a script:

          set variable [ $range ; table::startmonthglobal & "..." & table::endmonthglobal ]

          and then script your find to find based on monthid -> $range.

          • 2. Re: Graph Help
            RishiKhanna

            Should I put these fields in a separate table? Also how should i turn the numbers into months?

            Yes we are going by the current year, also I was going to add prior year sales into the chart as a comparison. I assume this should work too?

            • 3. Re: Graph Help
              mikebeargie

              you said you already calculated a field called monthid that stores the month number, so you'd be searching that field with the number selected from the global fields. The global fields can be from whatever table you want really, they are only used as temporary values.

               

              It might be easier for you to go by start date / end date, rather than trying to calculate month separately, if you don't understand some of the basic date functions.

              • 4. Re: Graph Help
                RishiKhanna

                Everytime i make a dropdown, say for monthId, I would link it to a different table as a value list with fields called monthId and monthName. It always changes the original value is there a way to restrict it not to save that, but still specify the variable selected

                • 5. Re: Graph Help
                  mikebeargie

                  you're using the word variable but I don't think that's what you need. Can you upload a sample of your file and make some notes on the layout as to what you are trying to do? That would be the best way to communicate. I don't think you understand the concept of using global storage fields as selectors for your report parameters.

                  • 6. Re: Graph Help
                    RishiKhanna

                    Its got some company data in it, so I cant upload it unfortunately, ill try out what you said and see what happens

                    • 7. Re: Graph Help
                      RishiKhanna

                      Mike, you are a god.

                       

                      I got it working, global fields are absolutely amazing

                      • 8. Re: Graph Help
                        RishiKhanna

                        Btw, I had the value list go to monthId and then monthName, is there a way to sort by monthId instead of monthName, so its in the correct order

                        • 9. Re: Graph Help
                          mikebeargie

                          in the value list configuration options you should be able to set sort by either the first or second field.

                          • 10. Re: Graph Help
                            RishiKhanna

                            It unhighlights that option when I choose only show second, I just wanted to show the name