Set Analysis Help !!!

[#1]

Hi All

I want this expression to sum all [Claim Transaction Payments] where the [Claim Transaction Sequence Number] is = to the Maximum [Claim Transaction Sequence Number]

sum({$<[Claim Transaction Sequence Number] = {'max[Claim Transaction Sequence Number]'}>} [Claim Transaction Payments])

If you can think of an alternative method that would be great.

Many thanks in advance

[Miguel_Angel_Baeyens]

Hello,

I'd use something like

sum({$<[Claim Transaction Sequence Number] = {'$(=max({1} TOTAL [Claim Transaction Sequence Number]))'}>} [Claim Transaction Payments])

If the result is numeric, then you don't have to single quote the $() part.

Hope that helps.

[#3]

Thanks you for your reply

It is a numeric value. Althogh when i implement your suggested expression, there it returns values of zero for every record.

Regards

[#4]

Hello! i did some fine tuning on the syntax and got out with 2 suggestions

This one will return a value if the max(claim transact seq no)of all records (regarding the current selection) is in the selected or possible values
sum({$<[Claim Transaction Sequence Number] = {"=$(=max({1} TOTAL [Claim Transaction Sequence Number]))"}>} [Claim Transaction Payments])

This one will return value for the max(claim transact seq no) of the current selection
sum({$<[Claim Transaction Sequence Number] = {"=$(=max([Claim Transaction Sequence Number]))"}>} [Claim Transaction Payments])

Good luck 🙂

[#5]

If you want a different max claim transaction sequence number for every row then you'll have to do something tricky with aggr but you didn't really give enough details to indicate whether that is what you need or if set analysis will work.

[#6]

Here is my data set with an explanation as to what i need to do

Your help is much appreciated

[#7]

Well you could just add something like this:

LEFT JOIN LOAD

[Claim ref],

max([Claim Transaction Done Date]) as 'Max Done Date'

RESIDENT whateverthetablenameis

GROUP BY [Claim ref];

LEFT JOIN LOAD

[Claim Ref],

if([Max Done Date]=[Claim Transaction Sequence Number],1,0) as 'Max Flag'

RESIDENT whateveryourtablenameis;

Then you just select max flag = 1 or just put it in the set analysis I guess. I'm pretty terrible with the script and joins so it's possible I screwed something up but I've done something similar to this many times and it works for me. Basically you'll get:

[Claim ref:] [Done Date] [Amount] [Max Done Date] [Max Flag]

A 1/1/2007 50 3/1/2007 0

A 2/1/2007 25 3/1/2007 0

A 3/1/2007 80 3/1/2007 1

I think if you look at the table above it's pretty easy to understand. You could probably do something better with inner join where you wouldn't need a flag but I'd probably mess that up.

Another solution that avoids altering the script:

Claim ref = Dimension

Expression:

=
sum
(
aggr
(
if([Claim Transaction Done Date]=max(total <[Claim ref]> [Claim Transaction Done Date]),
[Payments]
)
,[Claim ref],[Claim Transaction Done Date]
)
)

You might need to do something about the format of the [Claim Transaction Done Date] field but I don't know. If you want two transactions on the same day but different times to be added, then you probably need to take the time out with like left([Claim Transaction Done Date],10) as 'Claim Transaction Done Date' and then rename the other one to something with time in it.

Hope that helps