Field value corresponding to highest possible value of another field

[#1]

Hi,

I really hope somebody can help me here. I thought this was going to be simple to do but am apparently missing something...

In the attached .qvw I have a straight table where I'm trying to show Status, Completion and Revenue values for each Project but only where Cal_ID has the highest possible value for each specific Project and within the current selection.

I've tried using set analysis along with a max(Cal_ID) variable but that doesn't seem to work. I've also tried several combinations with aggr and rank but that didn't crack it either.

Can you help please?

Below is how the staight table should look in the end:

Project	Project Status	Completion to Month End (Fixed)	Fixed Revenue
Project A	Completed	100,00%	€ 12.500,00
Project B	Completed	100,00%	€ 30.000,00
Project C	Ongoing	50,00%	€ 18.224,00
Project D	Ongoing	92,00%	€ 249.432,36
Project E	Ongoing	95,00%	€ 15.992,00
Project F	Completed	100,00%	€ 12.950,00

Thanks!

[swuehl · Accepted solution]

Use FirstSortedValue() function to get the latest values with respect to Cal_ID, like

=FirstSortedValue([Project Status], -Cal_ID)

See also attached.

Stefan

[#3]

H Stefan,

Thanks.

What I really need is the highest Cal_ID to be picked, which is not necessarily the latest. Does this function pick the highest? Sorry, I've never used it...

Linda

[swuehl]

Sorry if this was misleading:

FirstSortedValue([Project Status], -Cal_ID) will indeed return the Project Status for the highest Cal_ID:

firstsortedvalue([{set_expression}][ distinct ] [ total [<fld {, fld}>]] expression [, sort_weight [, n]])

returns the first value of expression sorted by corresponding sort-weight when expression is iterated over the chart dimension(s). Sort-weight should return a numeric value where the lowest value will render the corresponding value of expression to be sorted first. By preceding the sort-value expression with a minus sign, the function will return the last value instead. If more than one value of expression share the same lowest sort-order, the function will return null. By stating an n larger than 1, you will get the nth value in order.

[#5]

Thanks!!!