Return Last Sting In Field

[#1]

Hi All,

I have this set of data

Claimant Type	Claim Number	Ver No	Payment Reserves	Recovery Reserves	Payments	RI Recoveries	Non RI Recoveries
AD	RS100202	1	925.00	0.00	0.00	0.00	0.00
AD	RS100202	2	925.00	0.00	0.00	0.00	0.00
AD	RS100202	3	925.00	0.00	0.00	0.00	0.00
PI	RS100202	1	4585.00	0.00	0.00	0.00	0.00
PI	RS100202	2	4585.00	0.00	0.00	0.00	0.00
PI	RS100202	3	4585.00	0.00	0.00	0.00	0.00
TP	RS100202	1	1000.00	0.00	0.00	0.00	0.00
TP	RS100202	2	1000.00	0.00	0.00	0.00	0.00
TP	RS100202	3	1000.00	0.00	0.00	0.00	0.00

I wish to return the maximum Ver No and then the corresponding data with that row ( See Below ). I have used the MAX function in an expression to select the maximum Ver no. What expression can i use to return the string which corresponds with the rest of the data on the Max Ver No field?

Claimant Type	Claim Number	Ver No	Payment Reserves	Recovery Reserves	Payments	RI Recoveries	Non RI Recoveries
TP	RS100202	3	1000.00	0.00	0.00	0.00	0.00
PI	RS100202	3	4585.00	0.00	0.00	0.00	0.00
AD	RS100202	3	925.00	0.00	0.00	0.00	0.00

Thanks in Advance

Regards
James

[Miguel_Angel_Baeyens]

Hello,

If you only want to show the product with the maximum sales, try Product as dimension and this as expression

Only({< Sales = {$(=Max(Sales))} >} Sales)

Hope that helps

[#3]

Hi Miguel

your solution only returns the reults for the AD & PI rows. i need for every row.

See attached file

[#4]

Hi,

see the attached example.

Good luck!

Rainer

[#5]

I love the solution!!!

but why doesnt it work when i do it as an expression???

I appreciate your help

[#6]

I love the solution!!!

but why doesnt it work when i do it as an expression? Where is the result for PI ??

I appreciate your help

[#7 · Accepted solution]

Hi,

it depends on the number of dimensions. See QV Help for more information about AGGR.

Getting the right lines during data reload is the easiest way and response time for end users are low.

Rainer

[Miguel_Angel_Baeyens]

Hello,

FirstSortedValue() returns one value, so does Max(), and it may require as much aggregations as fields you need to calculate. As you want to get the sum (aggregation) of values in Incurred when there may one or more records with the Max() value, so the following should work in the expression

Sum(If(Aggr(NODISTINCT Max([Claim Transaction Sequence Number]), [Claimant Type]) = [Claim Transaction Sequence Number], Incurred))

Hope this helps.

[#9]

Thank you Miguel

This seems to work. But only when there is one claim number. as soon as this expression is allocaed to a dataset with more than one claim number it stops working....

This is my fault as i didnt specify in my request or on the example. .

Regards

James

[Miguel_Angel_Baeyens]

Hello James,

Actually this expression is another option to what Rainer accurately suggests, meaning that depends on your datamodel and your chart the number of aggregations you have to do. Adding the "Claim Number" to the aggregation, the formula works:

Sum(If(Aggr(NODISTINCT Max([Claim Transaction Sequence Number]), [Claimant Type], [Claim Number]) = [Claim Transaction Sequence Number], Incurred))

Hope that helps.

[#11]

Hi

I am in a similar position to before. the difference this time is that i want to return the last text string instead of the last numerical value.

how could this be changed to return the last text string??

Sum(If(Aggr(NODISTINCT Max([Claim Transaction Sequence Number]), [Claimant Type], [Claim Number]) = [Claim Transaction Sequence Number], Incurred))

Many thanks James