4 Replies Latest reply: Jan 24, 2013 12:33 PM by Linda Diodato

# Field value corresponding to highest possible value of another field

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.

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!

• ###### Re: Field value corresponding to highest possible value of another field

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

=FirstSortedValue([Project Status], -Cal_ID)

Stefan

• ###### Re: Field value corresponding to highest possible value of another field

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

• ###### Re: Field value corresponding to highest possible value of another field

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.

• ###### Re: Field value corresponding to highest possible value of another field

Thanks!!!