Bar Chart is not grouping correcly

TeeChart FireMonkey (Windows,OSX,iOS & Android) for Embarcadero RAD Studio, Delphi and C++ Builder (XE5+)
Post Reply
realsol
Newbie
Newbie
Posts: 70
Joined: Mon Feb 27, 2017 12:00 am

Bar Chart is not grouping correcly

Post by realsol » Thu Mar 16, 2017 5:15 pm

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?
Attachments
2017-03-16_1003.png
Multiple Years
2017-03-16_1003.png (48.93 KiB) Viewed 9266 times
2017-03-16_1001_001.png
Last year
2017-03-16_1001_001.png (53.24 KiB) Viewed 9263 times

Yeray
Site Admin
Site Admin
Posts: 9509
Joined: Tue Dec 05, 2006 12:00 am
Location: Girona, Catalonia
Contact:

Re: Bar Chart is not grouping correcly

Post by Yeray » Fri Mar 17, 2017 11:25 am

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.
Best Regards,
ImageYeray Alonso
Development & Support
Steema Software
Av. Montilivi 33, 17003 Girona, Catalonia (SP)
Image Image Image Image Image Image Please read our Bug Fixing Policy

realsol
Newbie
Newbie
Posts: 70
Joined: Mon Feb 27, 2017 12:00 am

Re: Bar Chart is not grouping correcly

Post by realsol » Fri Mar 17, 2017 11:42 am

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.

Post Reply