Chart formatting with Dynamic Dates

[hobanwashburne]

I have a bunch of small questions about Chart formatting and Dynamic dates that I thought I would combine into one. The attached file contains 2 tables: 1) The sales plan from 201207 thru 201312 and 2) the actual year to date sales (201207 thru 201307). I want to display Actual vs Plan for the previous 9 months only (201211 thru 201307). I also want the current month to display as a bar graph (because it is not yet over). Also 9 months crosses from 2012 to 2013. It would be nice to have a vertical line at that point to denote the new year. I’m pretty sure that I could get this done if the data was static, but when I reload this on 8/1/13 I want the new date range displayed, actual sales from July to now be shown as a line along with all the previous months and actual sales from August to now be shown as a bar. There are also three text boxes. The first should display the current month’s sales. The second should display the planned sales. The third should display the difference between the two values.

Extra points if you can add a slider that changes the range of the chart so that it isn’t locked to 9 months.

[Gysbert_Wassenaar]

Your month field contains strings. If you make dates from that it becomes possible to calculate with them. See attached example.

[hobanwashburne]

This was very helpful, and I understand the date/string
problem. Thank you for pointing that out.

But forgive me if I can’t quite follow along with the data formatting.
In your example you created new QVDs from which to pull the data into a single
table. How should the script look if you were pulling in the data from two (crosstable)
Excel files? Or in the case of the actual project the ‘Plan’ file will come
from Excel and the Sales data will come from an ODBC table via SQL.