Hi all,
I need help 🙂
I have a very simple table with 24 rows (one for each month from Jan 2013 to Dec 2014) and 3 columns (goal, projection, actual) that I am using for a line chart. The chart shows the goal and the projection lines for the the entire data set (Jan 2013 to Dec 2014) and the actual line for what we know (Jan 2013 to Nov 2013).
I do not want to add values to all data points because it would clutter the chart, but I want to tell the reader exactly where we are today (value of actual for this month) and where we are going (value of projection for next month). I plan to do this by adding a text box in the chart (presentation tab, "text in chart") and I am struggling with the set analysis syntax.
Because I have data points for the goal and the projection all the way to Dec 2014, I cannot use
='Actual: ' & sum({$<month={"$(=max(month))"}>} actual
and because the actual progression is not linear, I cannot use
='Actual: ' & max(actual)
So I thought about using the n-th item in my list of actuals. I know what the values I want to use are the latest in the actual column and the next for the projection column, which I can figure out with =COUNT(actual) and =COUNT(actual)+1, but I do not know how to say in my expression "use the (count(actual))-th item from the field named actual" and "use the (count(actula)+1)-th item from the field named projection".
Any suggestions?
Thanks!
Marina
