1 2 Previous Next 18 Replies Latest reply: Feb 18, 2014 1:32 AM by Barry
4thSpace Level 1 Level 1 (0 points)

I have data points for several months. Sales by day for example.  I'd like to plot all months on one calendar for comparison.

 

Since line charts can't display each day of the month for several months of data, is there a way to have the X-axis plot 1 thru 31 (just days, no months)?  This way, regardless of the month, each day during some month will correspond to the correct day on the chart.  This will align all months.

  • 1. Re: How to set fixed days of month for multiple months?
    Yellowbox Level 5 Level 5 (4,570 points)

    Hi 4,

     

    Screen Shot 2014-02-07 at 5.13.20 pm.png

     

    Dates in Column A. Formula in B2 (and Fill Down) is:

     

    =DAY(A2)

     

    That will give a number corresponding to the day of the month.

     

    B is a Header Column, as required for a Line (category) graph. Select Columns B and C then insert your graph.

     

    Regards,

    Ian.

  • 2. Re: How to set fixed days of month for multiple months?
    Barry Level 7 Level 7 (29,180 points)

    Here are two examples. Is this what you are looking for?

    Screen Shot 2014-02-06 at 11.57.19 PM.png

    Example 1

     

    Screen Shot 2014-02-06 at 11.58.58 PM.png

    Example 2a

    Screen Shot 2014-02-07 at 12.07.30 AM.png

    Example 2b

     

    Both charts use the same data set (for Y values), contained in columns C (Jan) through N (Dec) in the table below.

     

    Example 1 is a Line chart. This is a Category chart—th Y axis is a value axis, and uses the values listed in columns C through N, the X axis is a category axis, and uses the Category Labels in  column A (a Header column) as category labels on the X axis of the chart. (Column B is included on the table for use in the second set of examples, and is NOT used in this chart.)

     

    Examples 2a and 2b are Scatter Charts. Both axes are value axes. The Y values are contained in columns C through N. The X values are listed in Column B (a non-header column), and are shared by all series of Y values.

     

    Either type will do the job you want here, assuming I've read your question correctly. The major difference between them for this task is that Category charts will place a label on the x axis for every category, provided there is room to do so.

     

    Value axes permit up to 10 steps in the range of values shown on the axis. For a range of values from 0 to 31 (a prime number), this can be a problem. If you want to use a zero value for the left edge of the chart, you will need to use a value other than 31 for the maximum in order to get integer labels along the x axis. In example 2a, I've set the maximum to 35, the number of steps to 7, and used a borderless white rectangle to cover the right end of the chart. For the example, I've set the opacity of the rectangle to 90% to permit you to see what's being covered.

     

    In Example 2b, I reset both minimum and maximum values for the x axis to 1 and 31 respectively. This makes it possible to show integer labels with 2, 3, 5, 6 or 10 steps. Here I've used 10 steps to show a label every three days.

     

    Regards,

    Barry

     

    PS: Table and chart done in Numbers '09 (Numbers 2). Numbers 3 may allow more steps in the value axes.

  • 3. Re: How to set fixed days of month for multiple months?
    4thSpace Level 1 Level 1 (0 points)

    @YellowBox: I'm not sure I follow what you are doing.  Why would 4/1 be day 4?  Or 5/1 be day 5?  That doesn't make any sense.  Each of those are day 1.

     

    Would it be possible to create a new column at have it display just the day part of the date value?  For example:

     

    2/1 = 1

    2/25 = 25

    3/4 = 4

    3/20 = 20

    etc.

  • 4. Re: How to set fixed days of month for multiple months?
    4thSpace Level 1 Level 1 (0 points)

    @Barry: I have no idea how you are doing that.  Do you have a spreadsheet I can download?

  • 5. Re: How to set fixed days of month for multiple months?
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    Barry has plotted a Series for each Month of the year.

     

    Jerry

  • 6. Re: How to set fixed days of month for multiple months?
    Yellowbox Level 5 Level 5 (4,570 points)

    =DAY(A2) is working correctly on the day/month/year date format used in my regional settings.

     

    Ian.

  • 7. Re: How to set fixed days of month for multiple months?
    SGIII Level 5 Level 5 (4,130 points)

    Why would 4/1 be day 4?  Or 5/1 be day 5?  That doesn't make any sense.  Each of those are day 1.

     

    It's interesting to look at date formats used around the world. According to statistics here, over ten times more people use Ian's DMY date format than MDY.

     

    SG

  • 8. Re: How to set fixed days of month for multiple months?
    4thSpace Level 1 Level 1 (0 points)

    Nice Barry.

     

    In this spreadsheet https://www.dropbox.com/sh/b73uim1h99o17on/DnnYL5skq-, how do I get the x-axis to display days rather than days from January? 

     

    If the x-axis displayed days 1-31, it would be month agnostic.  Then any month plotted on the chart will be relative to days.

  • 9. Re: How to set fixed days of month for multiple months?
    Barry Level 7 Level 7 (29,180 points)

    "@Barry: I have no idea how you are doing that.  Do you have a spreadsheet I can download?"

     

    Hi 'space,

     

    My bad! Neglected to include the table in my earlier post. These are reconstructions, and have different values, as they are random numbers within a patten in both cases.

     

    The images include the table. In each image one of the charts in selected, and the selection and settings on the table are shown or described.

    Screen Shot 2014-02-09 at 6.10.25 PM.png

    Line chart: Columns C (Jan) to N (Dec) selected. The chart gets its Data Series labels from Row 1 (a Header row) and its Category labels from Column A (a Header column). Column B is not used for this chart.

    Screen Shot 2014-02-09 at 6.09.44 PM.png

    Scatter Chart: Data selection includes column B, a non-header column (Date) through column N (Dec). These will initially be treated as data pairs (B-C,D-E, etc.). Click the gear icon at top left and choose Share x values. X values are then take from only column B, and each of the month columns is used as a separate series on the chart. Column A is not used in this chart.

     

    Other details are covered in my previous post.

     

    The 'spreadsheet' is a single table, with values in cells as shown. Month names and day numbers were entered directly. Values in the cells in columns C through I were created with a random number formula, biased with a fixed value and augmented with a second value dependent on the distance in days from midmonth to insure the values followed a discernable pattern. The actual values have no meaning, and wouldn't be of use to you in that regard.

     

    For your case, you would want a summary table to collect the daily figures for each day into a table resembling the one I used. For the benefit of others searching for a method to create such a table, this would be the subject of a new question.

     

    Regards,

    Barry

  • 10. Re: How to set fixed days of month for multiple months?
    4thSpace Level 1 Level 1 (0 points)

    Sorry.  In my last reply, I meant to say Yellowbox (not Barry) because of this:

     

    =DAY(A2) is working correctly on the day/month/year date format used in my regional settings.

  • 11. Re: How to set fixed days of month for multiple months?
    Barry Level 7 Level 7 (29,180 points)

    HI 'Space,

     

    =DAY(A2) will work on any cell containing a Date and Time value. "Format" does not change the value in a cell, just the way it is displayed.

     

    Regards,

    Barry

  • 12. Re: How to set fixed days of month for multiple months?
    Yellowbox Level 5 Level 5 (4,570 points)

    Hi 4thSpace,

     

    Screen Shot 2014-02-10 at 9.39.58 pm.png

     

    All cells from A2 down contain the same Date and Time value (the 10th day in the month of February, 2014 at midnight). Midnight is a default or "dummy" value (I entered the values at 9:30 pm). The cell formats have been changed to show various ways to display the value.

     

    Formula in B2 (and Fill Down)

     

    =DAY(A2)

     

    shows that the day of the month is 10 in all formats. As Barry said, the format does not change the value.

     

    Regards,

    Ian.

  • 13. Re: How to set fixed days of month for multiple months?
    4thSpace Level 1 Level 1 (0 points)

    Yellobox: That's a very simple case.  I have multiple months, which don't plot on a single chart.  I'm still not sure how to get them on the same chart with an absolute set of days (1-31) on the x-axis.

  • 14. Re: How to set fixed days of month for multiple months?
    Yellowbox Level 5 Level 5 (4,570 points)

    Hi 4thSpace,

     

    In your spreadsheet https://www.dropbox.com/sh/b73uim1h99o17on/DnnYL5skq-, how do I get the x-axis to display days rather than days from January?

     

    I think you are using a Category (LIne) Graph and not a Scatter Plot. Each table in your spreadsheet shows a month with different dates (Categories, fixed spacing on the X axis), rather than Date values (to make a linear scale).

     

    If you want to plot sales by day with each month as a series, you will need to reorganise your data as suggested by Barry.

     

    Regards,

    Ian.

1 2 Previous Next