2 Replies Latest reply: Oct 2, 2012 5:12 PM by w00master

# Displaying Field Values in TextBox

Hey Everyone-

I have this table:

 Run Name Run Date No. of Runners May 2011 Run 5/1/2011 1,952 June 2011 Run 6/1/2011 3,075 July 2011 Run 7/1/2011 4,724 August 2011 Run 8/1/2011 163 September 2011 Run 9/1/2011 1,977 October 2011 Run 10/1/2011 1,072 November 2011 Run 11/1/2011 1,156 January 2012 Run 1/1/2012 1,593 February 2012 Run 2/1/2012 1,070 March 2012 Run 3/1/2012 1,151 April 2012 Run 4/1/2012 1,244 May 2012 Run 5/1/2012 1,021 June 2012 Run 6/1/2012 654 July 2012 Run 7/1/2012 627 August 2012 Run 8/1/2012 483 September 2012 Run 9/1/2012 0

In order to calculate a "baseline," whatever Run Name a user selects in a list box, I have QV average the No. of Runners of the 12 *previous* Run Name(s) (and not including the selected Run Name).  In otherwords, if they choose "June 2012 Run," QV averages the No. of Runners from the Run Name of May 2011 Run to May 2012 Run.  Likewise, if they choose "August 2011 Run,"  No. of Runners will be averaged from Run Name "May 2011 Run" to "July 2011 Run."

That said, I have all of my aggregation functions working properly, however I want to *display* the Run Names contained in the baseline within a Text Box saying (in the Case of choosing "August 2011 Run"):

Baseline is calculated from the "May 2011 Run" to "July 2011 Run."

A couple of other twists:

1. The average should not exceed the 12 "Run Name." In other words, in my average calculations, if user selects "September 2012 Run" the baseline average will be from "August 2011 Run" to "August 2012 Run"

2. Notice that there is no "December 2011" run.

How do I accomplish this?

Thanks!

• ###### Re: Displaying Field Values in TextBox

Assign a sequence number to the runs in order. You can do that with RecNo() during the load.

RecNo() as RunId

Then an expression like:

='Baseline is from '

& only({1<RunId={\$(=RangeMax(max(RunId)-12,1))}>}[Run Name])

& ' to '

& only({1<RunId={\$(=max(RunId)-1)}>}[Run Name])

-Rob

• ###### Re: Displaying Field Values in TextBox

Rob-

Thank you so much.  This is exactly what I needed.  This alone taught me a ton about RANGEMAX and ONLY. Thanks again.