1 2 Previous Next 20 Replies Latest reply: Dec 16, 2013 1:58 PM by rye-guy
BioRich Level 1 Level 1 (65 points)

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!
  • 1. Re: X Axis Date Doesn't Work
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    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

  • 2. Re: X Axis Date Doesn't Work
    BioRich Level 1 Level 1 (65 points)

    Wow.  I just got notification of a reply to my post, but when I come here, there's no reply at all.  I will try later.  Very strange. 

  • 3. Re: X Axis Date Doesn't Work
    BioRich Level 1 Level 1 (65 points)

    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

  • 4. Re: X Axis Date Doesn't Work
    Badunit Level 6 Level 6 (10,815 points)
    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

  • 5. Re: X Axis Date Doesn't Work
    BioRich Level 1 Level 1 (65 points)

    OK, now that did the trick.  I have that very thing in a table/chart trial right next to it, and it didn't work.  I just tried it again and it doesn't work.  This application always frustrates me.

     

    But, you did show me how it's done, so I should be able to run from here, even with a new table/chart creation.  Thank you for the guidance. 

     

    Cheers

  • 6. Re: X Axis Date Doesn't Work
    Wayne Contello Level 6 Level 6 (13,615 points)

    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

  • 7. Re: X Axis Date Doesn't Work
    BioRich Level 1 Level 1 (65 points)

    Choosing a scatter chart provides no leadership at all.  Nor does the documentation, that I could find. 

  • 8. Re: X Axis Date Doesn't Work
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    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

  • 9. Re: X Axis Date Doesn't Work
    BioRich Level 1 Level 1 (65 points)

    Jerrold.  I read your post the first time around, but just to entertain you, I read it again.  You still don't understand what I put.  So let me make this clear:

     

    1.  This application has this strange issue with "header columns" which places a restriction on what type of charts can be made from presented data.  That makes no sense whatsoever. 

     

    2.  I know the complete difference between the two types of charts.  Been using them for decades.

     

    3.  Your first type of chart has no application when you have a non-linear x-axis, which is assumed in your example.  Do you not see that problem? 

  • 10. Re: X Axis Date Doesn't Work
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    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

  • 11. Re: X Axis Date Doesn't Work
    BioRich Level 1 Level 1 (65 points)

    That doesn't work when choosing a line, only as scatter.  That's the point of scatter.  If chosen as a line, the dates are merely labels for indiscriminate values provided for the Y axis.

  • 12. Re: X Axis Date Doesn't Work
    Barry Level 7 Level 7 (29,180 points)

    "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

  • 13. Re: X Axis Date Doesn't Work
    BioRich Level 1 Level 1 (65 points)

    Barry wrote:

     

     

    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.

     

    The only saving grace here is that we can plot a small choice of trending lines, even point to point linear, which...turns it into a line chart. 

     

    In any case, I'm happy, things are fixed. 

     

    Cheers

  • 14. Re: X Axis Date Doesn't Work
    rye-guy Level 1 Level 1 (0 points)

    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