Show which Dimension has Greatest/Max Value

[#1]

I want to create a table that shows which Well has the greatest daily Depth_Drilled.

Well	Max Dly Depth Drilled
SRC 32H	3100

I'm able to get the maximum value in the database using the below in my expression tab...

     =Max({1} Total Depth_Drilled)

However, the table lists all wells (i.e., dimension) with that Maximum Depth_Drilled.

Well	Max Dly Depth Drilled
Mtn Range 12H	3100
Erie Cnty 5H	3100
SRC 32H	3100
Punxy 10V	3100
Durango 7V	3100

What is the code to only show the exact well/dimension with the maximum Depth_Drilled?  Also, if I wanted to show which well had the maximum within the last six months what is the code to show that timeframe?

Thanks,

Mindy

[swuehl · Accepted solution]

Remove the TOTAL qualifier from your expression and use a dimension limit in your straight table chart properties to show only the top value.

[#3]

Thanks Swuehl, this works but not in the way that I need it. I want this table chart to always show the well with the max depth no matter what other selections are made by the user.

[#4]

Never mind.  I got it to work as you described for the historical pull.  Thanks.

What about the data pull for the last 6 months?

[swuehl]

Yes, should be taken care of by your use of set identifier {1}

[swuehl]

You haven't told much about the date dimension in your data model, so just guessing.

Try something like

  =Max({1<Date = {">=$(=AddMonths(Today(),-6))"}>} Depth_Drilled)

[#7]

The date dimension code doesn't work.  It brings me the max depth without taking the 6-month timeframe into consideration.

[swuehl]

It's probably just a matter of formatting or interpreting your dates correctly.

But it's not really a fun game to guess what the problem might be, without knowing anything about your model and data. Why don't you come up with some sample data, best by posting a small sample QV app.

Or just select the last 6 month in your date dimension and then you will get your result (after removing the {1} set identifier).