Skip navigation

X Axis Date Doesn't Work

1050 Views 20 Replies Latest reply: Dec 16, 2013 1:58 PM by rye-guy RSS
1 2 Previous Next
BioRich Level 1 Level 1 (65 points)
Currently Being Moderated
Nov 19, 2012 5:37 AM

Hi folks.  I was told to start a new thread on this one.  So here goes.

 

I have a date column (not month, but the full date, no time) then a column for some text, then an amount (number), then a running count (number). 

 

I want to select first column, then last column, being a date, and a number.  I want to put them into a line chart, with dates properly spaced on the X axis. 

 

I choose those two columns, try to make a scatter chart.  I get numbers on the bottom, with no ability to choose the date column in the table, and no ability to choose the date as the X axis...it's so wrong I have no clue what's happening here. 

 

Is it possible to get this type of chart going? 

 

Cheers

Mac Pro, OS X Mountain Lion (10.8.2), Hypercard UG!
  • Jerrold Green1 Level 7 Level 7 (28,190 points)
    Currently Being Moderated
    Nov 19, 2012 8:51 AM (in response to BioRich)

    Hi Rich,

     

    You may be missing one important distinction between Line Charts and Scatter Charts. It's a rather subtle difference until you get a feel for it. In LIne Charts, one type of Category Chart, the "X" values are text and are best presented in a Header Column. In Scatter Charts, the X part of the X-Y pairs is in a non-Header column.

     

    Here's a small table and chart that I made in about a minute using roughly your workflow, but being careful to use the proper type of column:

    Screen Shot 2012-11-19 at 11.47.07 am.png

     

    Regards,

     

    Jerry

  • Badunit Level 6 Level 6 (10,760 points)
    Currently Being Moderated
    Nov 19, 2012 11:43 AM (in response to BioRich)
    1. Scatter chart.
    2. X and Y data must be in "normal" cells, nothing in header cells (unlike the other charts that need the X data to be in a header cell)

     

    #2 is where you are going wrong.

    Screen Shot 2012-11-19 at 2.41.50 PM.png

  • Wayne Contello Level 6 Level 6 (12,650 points)
    Currently Being Moderated
    Nov 19, 2012 4:23 PM (in response to BioRich)

    Best way to figure out how a chart sould be "constructed" is to select a chart from the chart menu WHEN NOTHING ELSE IS SELECTED.  This will insert a table AND a chart

  • Jerrold Green1 Level 7 Level 7 (28,190 points)
    Currently Being Moderated
    Nov 20, 2012 3:16 AM (in response to BioRich)

    BioRich wrote:

     

    Oh there it is.  Hehe.  I got the email before the reply was even posted.

     

    Hi Jerrold.  Thanks for the reply. 

     

    The problem is that the date entries are not discrete equi-spaced durations. 

     

    Nov 18, 2012

    Nov 23, 2012

    Jan 13, 2013

     

    Your chart is relying on the fixed duration between x-values.  I can't do that, hence the reasonability on the scatter chart.  I'm using the linear trendline to demonstrate a line chart in this instance.  Pic 1:

     

    Screen Shot 2012-11-19 at 11.59.05 AM.png

     

     

    Second, choosing scatter when selecting the first and third columns, gets me the following:

     

    Screen Shot 2012-11-19 at 12.00.54 PM.png

     

    Screen Shot 2012-11-19 at 11.59.31 AM.png

     

    That first column is indeed chosen as a date.  This chart should work, and shoving around locations in a spreadsheet, feels like a FileMaker workaround.  Rather bizarre dependencies/rules to be restricting a spreadsheet.  But that's more of an Apple thing.  Just wanting to know the workaround for this. 

     

    Cheers

    Rich,

     

    Please re-read my post.

     

    Jerry

  • Jerrold Green1 Level 7 Level 7 (28,190 points)
    Currently Being Moderated
    Nov 20, 2012 8:11 AM (in response to BioRich)

    BR,

     

    My advice to you was identical to Badunit's, and I didn't yet have the benefit of your screen shot that proved conclusively that you were using the incorrect type of column for your X Data.

     

    1. How Apple chose the behavior of the Header and Body columns with respect to chart types isn't my concern, I was just explaining to you how they work.

     

    2. I wasn't questioning your knowledge of the chart types, but rather explaining the peculiar column type behavior that you would need to know about in order to be successful.

     

    3. My chart example, the first and only one I gave you, works for any interval of X values. Here is the same chart with different dates:

    Screen Shot 2012-11-20 at 11.06.09 am.png

     

    Regards,

     

    Jerry

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Nov 20, 2012 11:40 PM (in response to BioRich)

    "That doesn't work when choosing a line, only as scatter." Correct. A "Line Chart" is a Category chart. Category charts have one value axis and one category axis. A data point's position on the value axis is determined by the value of the datum. The same data point's position on the category axis depends on it's category's position in the column listing the categories. Categories are placed in order of the list, and are equidistant from each other.

     

    "That's the point of scatter." Correct again. A Scatter chart has two value axes. Each data point's position is determined by a pair of values recorded in two, usually adjacent, columns of a table

     

    "If chosen as a line, the dates are merely labels for indiscriminate values provided for the Y axis." More or less correct. I'd omit the adverb and adjective. The dates, or whatever text is used, are labels (or names) for the categories to which the values in the series belong.

     

    If the dates are 'categories', then a category chart is an appropriate choice (although I'd be unlikely to recommend using a Line chart—just being on the list doesn't make it suited to the job).

    If the dates are 'values', then a category chart is not an appropriate choice; use a scatter chart to plot the data pairs. There is an option to connect the data points with straight line segments, or with your choice of a variety of curves.

     

    qv: Can I create a chronological X axis without all the data?

     

    Regards,

    Barry

  • rye-guy Level 1 Level 1 (0 points)
    Currently Being Moderated
    Dec 13, 2013 12:29 PM (in response to BioRich)

    Having the same issues.  I can't seem to get a date nor date&time to work as an x-axis no matter how much fiddling I do.  Very irritating.  This should be a lot easier and work a lot better.

1 2 Previous Next

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.