Alpha Video Training
Results 1 to 10 of 10

Thread: XY Chart problem with International Date format

  1. #1
    Member
    Real Name
    Gerard van Goor
    Join Date
    Jul 2012
    Location
    Sydney, Australia
    Posts
    32

    Default XY Chart problem with International Date format

    Hello Brains trust,

    I'm trying to create line-chart in UX, with Chart Control, of transactions occurred over a time period. DB is MySQL and SQL format for dates is yyyy-mm-dd. In UX Properties I have set date format to dd/MM/yyyy

    A standard line chart seems to work fine, however it does not spread dates evenly on X-Axis. With XY chart this spreading works nicely.
    However XY interprets 09-01-2018 as 1st Sept, no matter what I do. So my XY by chart looks like image attached.

    Annotation 2019-04-22 091454.jpg

    Any suggestions would be greatly appreciated.

    Rgds, GvG

    PS: Using Build 5667_5254

  2. #2
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,702

    Default Re: XY Chart problem with International Date format

    I'm terrible at Alpha charts... they are clear as mud to me. I tried to create an example... but it doesn't work... I'd have to re-learn the Chart control again... for the 10th time.
    How is your Data Series set up... and how is your Graph Data dialog set up?

    Try changing your SQL select statement... reformatting the date...

    Code:
    SELECT  DATE_FORMAT(ChartDate, '%d/%m/%Y')  AS NewChartDate
    and add in an ORDER BY.

    In my test chart, my dates are being averaged for some reason. I've got 10 dates in my data and only 5 averaged dates showing on the chart. Sheesh.
    Last edited by Davidk; 04-21-2019 at 11:57 PM.

  3. #3
    Member
    Real Name
    Gerard van Goor
    Join Date
    Jul 2012
    Location
    Sydney, Australia
    Posts
    32

    Default Re: XY Chart problem with International Date format

    Quote Originally Posted by Davidk View Post
    I'm terrible at Alpha charts... they are clear as mud to me. I tried to create an example... but it doesn't work... I'd have to re-learn the Chart control again... for the 10th time.
    How is your Data Series set up... and how is your Graph Data dialog set up?

    Try changing your SQL select statement... reformatting the date...

    Code:
    SELECT  DATE_FORMAT(ChartDate, '%d/%m/%Y')  AS NewChartDate
    and add in an ORDER BY.

    In my test chart, my dates are being averaged for some reason. I've got 10 dates in my data and only 5 averaged dates showing on the chart. Sheesh.
    Thanks David,

    My SQL query is:
    Code:
     SELECT id, value, description,  chart_date FROM chart ORDER BY chart_date
    Inside Series Editor, the order for chart_date looks correct:
    Annotation 2019-04-22 135308.jpg

    I also tried the following, however this did not change the outcome:

    Code:
    SELECT id, value, description,  DATE_FORMAT(chart_date, '%d/%m/%y')  AS NewChartDate FROM chart  ORDER BY NewChartDate
    Rgds, Gerard

  4. #4
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,702

    Default Re: XY Chart problem with International Date format

    Same here, the Data Series preview data is good. And my SQL Dates are plotted correctly, but the date points showing are averaged dates.

    It seems something is very buggy about this Chart Control. Twice now, it's hung Alpha. It can't be this hard to plot an XY Chart using this control.

    I would report it as a bug. The documentation is very much out of date. Perhaps it's no longer supported... and has been replaced by the Javascript Chart Control found in Other Controls.

    I just tried the Javascript Chart Control and got it working in a couple of minutes... and it displays properly as expected. It looks like the Chart Control is very buggy and Alpha has not kept it up to date. I couldn't even get titles working.

    JSLineChart.PNG
    Last edited by Davidk; 04-22-2019 at 02:34 AM.

  5. #5
    Member
    Real Name
    Gerard van Goor
    Join Date
    Jul 2012
    Location
    Sydney, Australia
    Posts
    32

    Default Re: XY Chart problem with International Date format

    Alpha are supplying a fix for this...

  6. #6
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,702

    Default Re: XY Chart problem with International Date format

    I've tried Alpha's fix, but I think I'm more confused than before. Here's is the fix doc...

    UX Component - Data Series - Date Values - By default, if you have a Data Series that is based on a SQL database, any date or time (date/time) values in the Data Series are rendered using the server's regional settings (i.e. MM/dd/yyyy for US or dd/MM/yyyy - international). However, in the case of the Chart control, it is necessary to provide data values in yyyy-MM-dd format. Therefore, the Data Series builder now allows you to define a date format.
    It seems that this means you must have your date format coming out of SQL as yyyy-MM-dd. Ok.

    But now the Data Series builder allows you to define a date format. What does this do?? Does this mean I can add a Date Format in the Data Series builder of dd-MM-yyyy? And that this format will show on my chart?

    Regardless of what date format I put into the Data Series builder, my Chart X Axis (the date) never represents the dates in my data... and... they are averaged. As well, I still can't get Titles to show on anything.

    Finally, this is the inline doc for the Data Series Date Format...
    ChartDateFormat.PNG

    This says... "If you are using the Date[sic] Series... you should use the yyyy-MM-dd format.". But... doesn't this defeat it's purpose? I'm confused. It doesn't really seem to matter what I put in the Date Format. And should this read "Data Series" not "Date Series"? Or is there actually a "Date Series"?

  7. #7
    Member
    Real Name
    Gerard van Goor
    Join Date
    Jul 2012
    Location
    Sydney, Australia
    Posts
    32

    Default Re: XY Chart problem with International Date format

    The Date format is in the Data Series, not the Chart X-Axis., they have added this field below the SQL definition It seems to be working fine for me now.

  8. #8
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,702

    Default Re: XY Chart problem with International Date format

    Understood about where the new "Date Format" is. What value are you using there?
    Are you seeing X-axis values for each of your dates? I'm getting averages. The data points are where my dates would be, but my actual dates are not on the X-axis.
    Also, are you able to add a Title anywhere? I include a title but it never shows.

  9. #9
    Member
    Real Name
    Gerard van Goor
    Join Date
    Jul 2012
    Location
    Sydney, Australia
    Posts
    32

    Default Re: XY Chart problem with International Date format

    Nope, I can't find the Title either.

    Re dates scaling, that is I think, and hope, how XY is meant to work. For short date-ranges, it does show the actual measurement points, however when range is too wide this does not work. If you need all your dates to show on X-Axis, use a line-Chart rather than XY-Chart
    Image 1.JPG
    Image 2.JPG

  10. #10
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,702

    Default Re: XY Chart problem with International Date format

    Thanks for confirming. Seems it should be the other way around. I've only got 9 dates. Not sure about this control... seems off.

Similar Threads

  1. Multiple International Date formats - in a single web application
    By workaholic06 in forum Mobile & Browser Applications
    Replies: 2
    Last Post: 10-04-2016, 10:44 PM
  2. International date xbasic help please
    By pcaspa in forum Mobile & Browser Applications
    Replies: 1
    Last Post: 05-08-2015, 11:51 PM
  3. Grid format international currency
    By PhilM1965 in forum Application Server Version 11 - Web/Browser Applications
    Replies: 4
    Last Post: 08-17-2012, 06:01 PM
  4. UK/US date format problem using date picker
    By Robert Rough in forum Application Server Version 8
    Replies: 4
    Last Post: 06-14-2007, 10:55 AM
  5. Date format problem
    By cellboy in forum Alpha Five Version 7
    Replies: 1
    Last Post: 03-30-2006, 05:50 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •