I have 2 questions regarding charting using the ExecuteSQL function. What I want to do is create a line chart that displays sales by salesperson over a specific period of time (attachment 1 is as far as I've gotten).
The X axis works fine and is a data set of the months selected by the user.
My issue is with the Y Axis. I created 3 data series, one for each salesperson.
Issue 1. When I try to use Order By (as I've done successfully in the x axis) I get an error. It might have something to do with the fact that I'm counting here. Nevertheless, I need the data series to be in order of month or its useless.
Issue 2. Not all salespeople have sales every month. That means that a data series that is reflecting 6 months could only be showing 3 numbers. The third number in the set could be the data from the 6th month but its appearing as the third. Is there a way to have "fillers" for sql data series where there is no data just so that its structured correctly? I hope that makes sense.
Here is the Y axis Calculation:
"SELECT Count("_k_ID_Location" )
WHERE "_id_salesperson" = ?
Group BY "sale_month_year"
//sales_month_year is a concatination of month and year.
//global fields are on the layout and constrain the time that appears in the chart, very cool.