AnsweredAssumed Answered

Issue with this year v last year chart

Question asked by duncanbaker on Apr 10, 2015
Latest reply on Apr 11, 2015 by 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

)

)

Outcomes