4 Replies Latest reply: Nov 4, 2016 5:22 PM by Jacinto Fernandez Nogales RSS

    Chart real vs budget with canonical calendar.

    Jacinto Fernandez Nogales

      Hi everyone,

       

      I have the following data model:

      File_Orders: this table contains the orders with date on which the order is received (date_in), date when the order request is delivered (date_out), amount of the sale made (Sales), etc.

      Budget: this table contains if it is Budget or butget update (Budget_Type), if the Budget is relative to the orders received (Order_type=IN) o delivered  (Order_type=OUT) and the Year, Month and Budget Sales values.

       

      I want a chart that when you select a month in the listbox (where the field of the listbox will be "MONTH FECHA CANONICA"), we will have two bar for month: one with the total of sales (DateType={'Date OUT'}) and other with de Budget_sales of each month (where Order_type=OUT).

      I don't know how link Budget table with file_orders table. (maybe I need to use generic keys ? but I don't know how...)

       

      Can someone help me ?

       

      Thanks in advance.

        • Re: Chart real vs budget with canonical calendar.
          Settu Periyasamy

          May be you can create one more field (Monthyear) in both the tables..(BUDGET and CALENDAR_CANONIC_

          Capture.JPG

           

          Check the attachment.

            • Re: Chart real vs budget with canonical calendar.
              Jacinto Fernandez Nogales

              Hi Settu,

               

              thanks very much for your answer. This option works correctly !!!

               

              A last question, please:

              If now I add another listbox with Product (field "Product" from the table FILE_ORDERS). And detail the budget month, adding the product, f.ex.:

              Load * Inline

              [

                  Budget_Type, Order_type, Year, Month, product, Budget_Sales

                  BUP, OUT, 2016, Jan, Chair, 50

                  BUP, OUT, 2016, Jan, Pen, 20

                  BUP, OUT, 2016, Jan, Book, 20

                  BUP, OUT, 2016, Feb, Chair, 10

                  BUP, OUT, 2016, Feb, Pen, 40

                  BUP, OUT, 2016, Feb, Book, 25

                  BUP, OUT, 2016, Mar, Chair, 20

                  BUP, OUT, 2016, Mar, Pen, 30

                  BUP, OUT, 2016, Mar, Book, 15...]

               

              Will works the same data model ?? I need when I filter for a certain product in the listbox, then on the chart display the sales of that product per month versus the budget for that product?

              I've explained ?


              Thanks you very much for the help !!!

              Jacinto.

                • Re: Chart real vs budget with canonical calendar.
                  Settu Periyasamy

                  I don't think it will work. already you have the 'Product' field in your 'FILE_ORDERS' table, right? you can select it from there, correct?

                    • Re: Chart real vs budget with canonical calendar.
                      Jacinto Fernandez Nogales

                      I have the "product" field in "FILE_ORDERS" table, but now I add also the product detail in Budget table. As I said in the last message the Budget table now will be:

                      BUDGET:

                      Load * Inline

                      [

                          Budget_Type, Order_type, Year, Month, product, Budget_Sales, Monthyear

                          BUP, OUT, 2016, Jan, Chair, 50, Jan2016

                          BUP, OUT, 2016, Jan, Pen, 20, Jan2016

                          BUP, OUT, 2016, Jan, Book, 20, Jan2016

                          BUP, OUT, 2016, Feb, Chair, 10, Feb2016

                          BUP, OUT, 2016, Feb, Pen, 40, Feb2016

                          BUP, OUT, 2016, Feb, Book, 25, Feb2016

                          BUP, OUT, 2016, Mar, Chair, 20, Mar2016

                          BUP, OUT, 2016, Mar, Pen, 30, Mar2016

                          BUP, OUT, 2016, Mar, Book, 15, Mar2016

                      ...]

                       

                       

                      Can you tell me how I can add a simple file qw for you can see what I'm saying ?? (now I don't see how to attach a file)

                       

                      Thanks !