10 Replies Latest reply on Apr 22, 2013 10:04 AM by philmodjunk

    Charting Sales by month over 2 years

    OmarFahmy

      Title

      Charting Sales by month over 2 years

      Post

           Hi 

           As per attached screenshot, I have the relationship graph to enable me to report the monthly sales over 2 years. 

           When I try to chart the data using the x-axis as Monthname(cMonth) and y-axis with 2 series ( series1: 2012, data: Unique_Cust::csumSiblingAmt_Cust (summary of total amount sales) and series2: 2011, data: Unique_Cust 2::csumSiblingAmt_Cust) the chart can only accept related records from one table ( either Unique_Cust or Unique_Cust 2) and this causes the values in the y-axis for one to display correctly but the other to be the same value for all months.

           can someone help out with this ?? 

            

      Sample.jpg

        • 1. Re: Charting Sales by month over 2 years
          philmodjunk

               For the layout where you are creating this chart, what, exactly, is selected in Layout Setup | Show Records from ?

          • 2. Re: Charting Sales by month over 2 years
            OmarFahmy

                 Layout is from Customers 3 

                 Showing only Customer Name and the chart 

            • 3. Re: Charting Sales by month over 2 years
              philmodjunk

                   And what calculation expression are you using th access the data in the two related table occurrences?

                   Does each record in the related table represent a single data point or do you need to compute monthly totals of multiple records in the related tables for each data point on your chart?

              • 4. Re: Charting Sales by month over 2 years
                OmarFahmy

                     - Fields/Calculations:

                     gYear1, gYear2 Global fields

                     OrderYear (from Order_Drct) = Year (OrderDate)

                     cMonth (from Order_Drct) = OrderDate-Day(OrderDate)+1

                     CondID_Cust (from CustOrders_DrctYear1) = Case (__pk_OrderDrctID = Sibling_Cust::__pk_OrderDrctID;Sibling_Cust::__pk_OrderDrctID)

                     -Relationships:

                     Customers3 ---- CustOrders_DrctYear1  ( __pk_CustomerID = _fk_CustomerID) AND (gYear1=OrderYear)

                     CustOrders_DrctYear1 ------ Sibling_Cust ( _fk_CustomerID = _fk_CustomerID) AND (cMonth = cMonth)

                     CustOrders_DrctYear1 ------ Unique_Cust ( CondID_Cust = __pk_OrderDrctID)

                     same applies for CustOrders_DrctYear2

                      

                • 5. Re: Charting Sales by month over 2 years
                  OmarFahmy

                       I need to compute monthly totals of multiple records in the related tables for each data point on the chart

                  • 6. Re: Charting Sales by month over 2 years
                    philmodjunk

                         Your relationships won't work for that as they match by customer and year. Totals computed from there would be for that customer for the entire year--one value per year per customer.

                         What kind of chart are you trying to produce and are CustOrders_DrctYear1 and CustOrders_DrctYear2 two separate tables or two occurrences of the same table?

                    • 7. Re: Charting Sales by month over 2 years
                      OmarFahmy

                           CustOrders_DrctYear1, CustOrders_DrctYear2, Sibling_Cust, Unique_Cust are all occurences of the the same table Orders_Drct

                           The chart produced is attached.. You can see that the values for 2012 are correct but the values for 2011 are all the same for all months 

                      • 8. Re: Charting Sales by month over 2 years
                        philmodjunk

                             I don't see how you got ANY correct data for a layout based on Customers 3 with the data to be charted in related tables using your relationships. What expressions/calculation fields are you using for the two data series?

                             I'd set up a chart like this from an occurrence of Orders_Drct, using a find to limit the records to one customer and a summary field to get sub totals for each of the bars in your bar chart. That approach takes a special calculation that combines month and year in just the right format as it requires sorting the records on a single field to get the parallel bars for each month like this.

                        • 9. Re: Charting Sales by month over 2 years
                          OmarFahmy

                               Expressions used for the chart:

                          x-axis as Monthname(cMonth)

                          y-axis  series1: 2012, data: Unique_Cust::csumSiblingAmt_Cust (summary of total amount sales)

                                      series2: 2011, data: Unique_Cust 2::csumSiblingAmt_Cust)

                                

                               Can you help with the " special calculation that combines month and year in just the right format as it requires sorting the records on a single field to get the parallel bars for each month like this " ? 

                                

                          • 10. Re: Charting Sales by month over 2 years
                            philmodjunk

                                 The only way that series 2 would yield correct results is if there is only one record for each customer for that year. Series 1, on the other hand would yield yearly totals.

                                 Let ( d = YourDatefield ; Right ( "0" & Month ( d ) ; 2 ) & Year ( d ) )

                                 Select Text as your result type.

                                 For January, 2012 dates, this produces: 012012 and it will sort January dates ahead of February dates but also separately groups the Janauary dates by year.

                                 You'd have a single Y axis data series and you'd use the found set/summarized groups option in your data source part of portal set up.