Skip navigation

Interactive Column Chart w/Sliding Date Range?

232 Views 3 Replies Latest reply: Dec 28, 2013 6:31 PM by Barry RSS
freediverx01 Level 1 Level 1 (40 points)
Currently Being Moderated
Dec 28, 2013 6:57 AM

I've created a stacked column chart in Numbers 3.0. It displays stacked data for a one week period using seven columns, one for each weekday. Is it possible to make an interactive version of this chart that would still display all seven stacked columns, but allow me to use a slider control to adjust the date range? Ideally I'd like the slider to adjust the date range one day at a time, so I have a rolling 7 day view of my data.

 

Any suggestions?


 

Screen Shot 2013-12-28 at 9.44.28 AM.jpg

 

 

 

Screen Shot 2013-12-28 at 9.44.54 AM.jpg

MacBook Pro with Retina display, OS X Mavericks (10.9.1)
  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Dec 28, 2013 3:58 PM (in response to freediverx01)

    Hi free',

     

    Use a summary table to collect the seven days of data, and run the charts from there. Place the starting date in the first data cell in column A (usually A2), either directly or using a slider or stepper to retrieve the date from the original data table or a lookup table consisnting of a list of numbers in column A and a corresponding list of dates in column B.

    Use =A2+1 to calculate the next date in A3, and fill down to the seventh row requiring a date.

     

    Use VLOOKUP or OFFSET to retrieve the data sets for the seven dates from the original data table.

     

    A more detiled answer can be supplied after seeing your screen shots, which are not showing up here.

     

    Regards,

    Barry

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Dec 28, 2013 6:31 PM (in response to freediverx01)

    Screen Shot 2013-12-28 at 6.18.12 PM.png

    Data is the original data table.

     

    Set Start is a four cell table with A2 set as a Slider, range 1 - 100, and the formula below in B2:

     

    B2: =OFFSET(Data :: $A$1,A,0)

     

    Cell B2 is for display only. It offers live feedback to the user while setting the beginning date for the chart, but is not used in the calculations in For Chart.

     

    For Chart is a Summary table containing the headers shown.

     

    There is one formula in this table, entered in A2, then filled right to D2, and down to row 8:

     

    A2: =OFFSET(Data :: $A$1,ROW()-2+Set Start :: $A$2,COLUMN()-1)

     

    The chart draws its information from For Chart. Here is a second chart image using the same data table. The only change is that the user has reset the slider to a larger value. The start date on this chart is the same as the last date on the one above.

    Screen Shot 2013-12-28 at 6.30.40 PM.png

     

    Regards,

    Barry

Actions

More Like This

  • Retrieving data ...

Bookmarked By (1)

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.