Select n-th value in a field

[#1]

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

[wallinpeter · Accepted solution]

Try this.....

It is a set statement within a set statement.

It selects the max(Month) from only the records that have an Actual value.

sum({$<month={"$(=max({$<Actual={">=$(=min(Actual))<=$(=max(Actual))"}>}month))"}>} actual)

[angelaecheverri]

sum(goal) = Show you all the months

If(Aggr(Sum(actual), Periodo) >0, Aggr(Sum(goal), Periodo))  = show you just sum of goals when in the moth are actual data.

[#4]

Thanks Peter 🙂

The expresion returns a value, but the value is exactly the one before the one I want.  What am I doing wrong?!!

[#5]

Gracias Angela 🙂

What do I use for "Periodo"?  I tried NOW() and TODAY() but the expression returns a "-"

[jerem1234]

Maybe something like this:

=subfield(concat({1}actual, ','), ',', count({1}actual))

and

=subfield(concat({1}projection, ','), ',', count({1}actual)+1)

Hope this helps!

[wallinpeter]

It is grabbing the Actual value for the wrong month?

Can you copy paste data and bold the the value you want returned.

[#8]

No!! My bad!

This works

Thanks Peter

[#9]

Thaks Jeremy.

The expression works too but it does not take into account selections (I forgot to mention: I allow readers to narrow down the year they want to see), so it always return the latest month, even if I select a different year.  Is there an easy way to tell the expresion to "stick to the selection" insead of "use the entire data set"?

Also, if you have a moment, could you please explain how "subfield" and "concat" work in this logic?

Thanks again 🙂

[jerem1234]

For something like this:

=subfield(concat({1}actual, ','), ',', count({1}actual))

the 1 will ignore all selections. If you want it to go with the selections, then remove the 1's.

=subfield(concat(actual, ','), ',', count(actual))

The concat function will combine all your field values into one string with the delimiter of ',' in this example.

(On a side note: You can also use the third parameter of concat to establish the sorting of the string.)

Then subfield will go through this string and get the count(actual)-th item using the , as a delimiter.

So if you had a field like Fruit:

Then did concat(Fruit, ',')

your result would be:

Apple, Orange, Peach, Banana

Then subfield(concat(Fruit, ','), ',' , 3)

would return:

Peach

Hope this helps!