Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Displaying Field Values in TextBox

Hey Everyone-

I have this table:

Run NameRun DateNo. of Runners
May 2011 Run5/1/20111,952
June 2011 Run6/1/20113,075
July 2011 Run7/1/20114,724
August 2011 Run8/1/2011163
September 2011 Run9/1/20111,977
October 2011 Run10/1/20111,072
November 2011 Run11/1/20111,156
January 2012 Run1/1/20121,593
February 2012 Run2/1/20121,070
March 2012 Run3/1/20121,151
April 2012 Run4/1/20121,244
May 2012 Run5/1/20121,021
June 2012 Run6/1/2012654
July 2012 Run7/1/2012627
August 2012 Run8/1/2012483
September 2012 Run9/1/20120

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!

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

2 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Not applicable
Author

Rob-

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