Page 1 of 1
Bar Chart is not grouping correcly
Posted: Thu Mar 16, 2017 5:15 pm
by 16880166
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?
Re: Bar Chart is not grouping correcly
Posted: Fri Mar 17, 2017 11:25 am
by 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.
Re: Bar Chart is not grouping correcly
Posted: Fri Mar 17, 2017 11:42 am
by 16880166
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.