9 Replies Latest reply on Apr 11, 2015 5:06 AM by duncanbaker

    Issue with this year v last year chart

    duncanbaker

      Hey folks

       

      I'm delving into charting and I'm having an issue with a chart that shows monthly sales for this year and last year. With the particular customer I'm looking at, they started ordering in September last year and I'm finding that last year's sales are showing up in Jan-Apr months on the chart. This is what I have. The OrderMonth/OrderYear fields are just Month ( OrderDate ) type calcs (result = number). Let me know if anything's not clear. Anyone got any ideas on what I'm doing wrong? I guess the problem is in the X-axis. Many thanks.

       

      X-axis (this is supposed to show all the month numbers that had sales in either this year or last year):

      Let ( [

      Today = Get ( CurrentDate ) ;

      ThisYear = Year ( Today ) ;

      LastYear = ThisYear -1

      ] ;

      ExecuteSQL("

      SELECT DISTINCT ( \"z_OrderMonth_c_n\" )

      FROM COR_CustomerOrders

      WHERE \"_zkfCustomerID\" = ? AND

      (

      \"z_OrderYear_c_n\" = ?

      )

      OR

      (

      \"z_OrderYear_c_n\" = ?

      )

      ORDER BY \"z_OrderMonth_c_n\"

      ";"";"";

      CUS_Customers::__zkpCustomerID ; ThisYear ; LastYear

      )

      )

       

      Y-axis 1 (this year sales):

      Let ( [

      Today = Get ( CurrentDate ) ;

      ThisYear = Year ( Today ) ;

      LastYear = ThisYear - 1

      ] ;

      ExecuteSQL("

      SELECT SUM ( \"OrderTotal\" )

      FROM COR_CustomerOrders

      WHERE \"_zkfCustomerID\" = ? AND

      \"z_OrderYear_c_n\" = ?

      GROUP BY \"z_OrderMonth_c_n\"

      ";"";"";

      CUS_Customers::__zkpCustomerID ; ThisYear

      )

      )

       

      Y-axis 2 (last year sales):

      Let ( [

      Today = Get ( CurrentDate ) ;

      ThisYear = Year ( Today ) ;

      LastYear = ThisYear - 1

      ] ;

      ExecuteSQL("

      SELECT SUM ( \"OrderTotal\" )

      FROM COR_CustomerOrders

      WHERE \"_zkfCustomerID\" = ? AND

      \"z_OrderYear_c_n\" = ?

      GROUP BY \"z_OrderMonth_c_n\"

      ";"";"";

      CUS_Customers::__zkpCustomerID ; LastYear

      )

      )

        • 1. Re: Issue with this year v last year chart
          erolst

          IIUC, your issue is that you have two y-axis series, and they overlap – though I'd expect to see the 2015 figures positioned above the 2014's months, not vice versa

           

          See if the attached file does what you need; it calculates the month count of the previous year to create placeholder positions that shift the start of this year's y-series to the correct position on the x-axis (corresponding to its first month).

           

          (I realize that this could (probably) be done directly in SQL, using code along the lines of

           

          " SELECT

            CASE

              WHEN year(dateOrder) = Get ( CurrentDate ) - 1

              THEN ' ' + CHAR(13)

              ELSE SUM (amountTotal)

            END

          FROM Orders

          WHERE Year(dateOrder) IN (" Year ( Get ( CurrentDate ) ) & "," & Year ( Get ( CurrentDate ) ) - 1 & ")

          ORDER BY …

          GROUP BY … "

           

          but couldn't get CASE and SUM() to play nicely.

           

          EDIT: On re-reading, the problem in your current calculations actually seems to be that you're sorting by the numerical cMonthNumber; so this series of matching distinct month numbers

           

          9,10,11,12,1,2,3,4

           

          will be sorted as

           

          1,2,3,4,9,10,11,12

           

          Solution would be to simply use the date field to ORDER BY in all your calculations.

          • 2. Re: Issue with this year v last year chart
            duncanbaker

            Thanks erolst. I tried changing the order by to the OrderDate and 9,10,11,12 came before 1,2,3,4 but all the amounts were in the first section. I thought it might help to see the chart . See below - with the data I have, all the blue should be over 9,10,11,12 and all the yellow should be 1,2,3,4 - it happens with this customer that their first sale was 9/2014. So I'd want to see zero for 2015 above 9,10,11,12 and zero for 2014 above 1,2,3,4. I'm trying to show a comparison of sales this year v last year. I don't get why the sales show in the wrong month. Thanks for the file. I think this is set up to show ongoing sales as a line chart - perhaps I could use the file to mock up my scenario and post it back.

             

            Should my X-axis just be a delimited list 1 through 12 perhaps?

            Chart.tiff

            • 3. Re: Issue with this year v last year chart
              duncanbaker

              Ok, so the delimited list of month numbers may be the way to go so all show up, but my sales for last year still show up in Jan-Apr not Sep-Dec...

              • 4. Re: Issue with this year v last year chart
                duncanbaker

                Ok, I think that the delimited sales data needs to have 12 values. Because there's no sales in Jan-Aug 2014, the eSQL just has something like:

                110

                125

                115

                145

                And this starts out at the beginning of the chart. What I think the eSQL should return is:

                0

                0 ... and 6 more zeros to represent all months up to Sep, and then

                110

                125

                etc

                 

                So, new question! How would I write an eSQL to evaluate sales for each month and return zero if there was nothing found for a particular month?

                • 5. Re: Issue with this year v last year chart
                  erolst

                  The only correlation between the x-axis and the values of the y-series is their list position; this means you need (as you have found out for yourself) to create placeholder values to properly align the y-values with the x-labels, either empty or zero.

                   

                  Try the attached file.

                  • 6. Re: Issue with this year v last year chart
                    user19752

                    When comparing to last year, usually there are at least longer than 1 year of data, so do you need calculating X-axis?

                    If use all 12 months in X, I did it a month ago in SQL: way to return 0 instead of null?

                    This can be modified for less than 12 months, but?

                    • 7. Re: Issue with this year v last year chart
                      erolst

                      user19752 wrote:

                      usually there are at least longer than 1 year of data, so do you need calculating X-axis?

                       

                      If I interpret the attached screenshot correctly, this is intended as multiple data series to compare over 12 months, so the x-axis can be a simple months list; the challenge is to include the list position for months with a null total, which can be done using a utility table (absent of being able to define an SQL variable as virtual pointer) to LEFT OUTER JOIN into the values table.

                       

                      PS: Thanks for the link; interesting discussion which I missed completely!

                      • 8. Re: Issue with this year v last year chart
                        duncanbaker

                        Thanks again erolst for taking the time to provide a sample file - had you said "create a utility table for months and do a left outer join to it", I'd have been stumped again! I did get this implemented late last night and it seems to work well. Problem solved, learned something new - the best of outcomes. Much appreciated.

                        • 9. Re: Issue with this year v last year chart
                          duncanbaker

                          Thanks for adding to this user19752. I've read that post and that's a cool solution that involves no additional table. I'm going to test this out as well.