topic Re: Show which Dimension has Greatest/Max Value in QlikView

Show which Dimension has Greatest/Max Value

Mon, 09 Sep 2013 19:02:00 GMT

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

Re: Show which Dimension has Greatest/Max Value

swuehl — Mon, 09 Sep 2013 20:00:18 GMT

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

Re: Show which Dimension has Greatest/Max Value

Mon, 09 Sep 2013 20:28:04 GMT

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.

Re: Show which Dimension has Greatest/Max Value

Mon, 09 Sep 2013 20:32:08 GMT

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?

Re: Show which Dimension has Greatest/Max Value

swuehl — Mon, 09 Sep 2013 20:33:16 GMT

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

Re: Show which Dimension has Greatest/Max Value

swuehl — Mon, 09 Sep 2013 21:12:54 GMT

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)

Re: Show which Dimension has Greatest/Max Value

Tue, 10 Sep 2013 17:12:19 GMT

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

Re: Show which Dimension has Greatest/Max Value

swuehl — Tue, 10 Sep 2013 17:49:12 GMT

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).