Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi Miguel
your solution only returns the reults for the AD & PI rows. i need for every row.
See attached file
Hi,
see the attached example.
Good luck!
Rainer
I love the solution!!!
but why doesnt it work when i do it as an expression???
I appreciate your help
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
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
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.
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
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.
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