Bar Chart is not grouping correcly

[realsol]

When I run this chart for only 1 year, it is fine. I group my X value by a combination of YEAR and MONTH. Currently, I am using that as my label while testing. But if I include more than one year in my Query, I get everything messed up.

The first image is from 1/1/2015 to 3/1/2017 (bad). The second is a query for last year(good, other than the bars not being side by side mentioned in another post). What do I need to change (either in the setting of the chart or my Query) to keep this from happening?

[Yeray]

Hello,

Are there gaps in the dates? If so, then the blank space between your bars may be correctly representing the gaps in the data.
If you want the same distance between two bars separated by a moth than between two bars separated by (ie) 5 months, an option would be to let the chart add the points sequentially by adding your values without XValue, and adding your dates as labels.

[realsol]

I had a feeling this might be the problem. I couldn't use 'real' dates for this chart because not all income or expense happens on the same day (the 1st of the month) and when grouping in SQL it takes the date of the first occurrence. So I create an integer, YYYYMM. But because of the integer gap between 201512 and 201601 was large, it created the blanks.

I then tried using altering my SQL statement:

Code: Select all

    SELECT *, ABS(SUM (AMOUNT)) AS ABSTOTAL,
      SUM (AMOUNT) AS TOTAL,
      (cast(strftime('%Y%m', date) as integer)) AS YEAR_MONTH,
      date(DATE,'start of month') AS MONTH_START,
      case strftime('%m', DATE) when '01' then 'Jan' when '02' then 'Feb' when '03' then 'Mar' when '04'
        then 'Apr' when '05' then 'May' when '06' then 'Jun' when '07' then 'Jul' when '08' then 'Aug' when '09'
        then 'Sep' when '10' then 'Oct' when '11' then 'Nov' when '12' then 'Dec' else '' end || "  '" || substr(strftime('%Y', date),3) as MON_YEAR,
      strftime('%d', date) AS DAY,
      (cast(strftime('%d', date) as integer)) / 7 + 1 AS WEEK,
      strftime('%m', date) AS MONTH,
      (cast(strftime('%m', date) as integer) + 2) / 3 as QTR
     FROM TRANSACTIONS
    INNER JOIN CATEGORIES
    ON TRANSACTIONS.TREE_NAME=CATEGORIES.CatName
    AND CATEGORIES.CAT_TYPE = 'Expense Categories'
    GROUP BY YEAR_MONTH
    ORDER BY YEAR_MONTH ASC, CATEGORIES.ROW_ID;}

But that didn't help because my MONTH_START was a WideString in SQL lite and didn't know a good CAST statement to convert it to a Date. So finally I used this created a Calc Field and within my oncalcfield event used this to create a date.

Code: Select all

  BarQueryMonthDate.Value := OnlineregForm.MyVarStrToDateTime(BarQueryMONTH_START.Value);

I used that as the field for X and now it works.