Interactive Column Chart w/Sliding Date Range?

Question

Level 1

132 points

Interactive Column Chart w/Sliding Date Range?

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?

MacBook Pro with Retina display, OS X Mavericks (10.9.1)

Posted on Dec 28, 2013 6:57 AM

Reply

Answer 1

Barry

Level 9

59,861 points

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

Reply

Answer 2

freediverx01 Author

Level 1

132 points

Dec 28, 2013 4:46 PM in response to Barry

OK, let me try to upload the images again, this time after scaling them...

Not completely clear on your instructions. I will be updating this table on a daily basis, and I'd like an interactive table that would display a seven day view that could be scrolled one day at a time.

Reply

Answer 3

Barry

Level 9

59,861 points

Dec 28, 2013 6:31 PM in response to freediverx01

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.

Regards,

Barry

Reply