Filtering Data with max

[#1]

Hello

I have following data :

OffNum      OffLine     Version     Amount

     1               1               1               10

     1               2               1               15

     1               1               2               9

     1               2               2               14

     2               1               1               4

     2               2               1               5

I would like to calculate the sum of the amount but only for the highest version. There is no limit in the numbers of OffNum, OffLine or Version. But I only need the highest version per offnum.

Anybody any idea?

Thanks

[whiteline]

You can use:

=Sum({$<Version={"=Version=Max(Version)"}>} Amount)

or

=Sum({$<Version={"=Version=Max(total Version)"}>} Amount)

depending on what your dimensions are.

[#3]

Can't get it to work. I've added a example with your answer.

Hope you can check it out.

[whiteline]

I said that depending on your needs you may have to add total qualifier.

Use the second expression from the first post:

Sum ({$<offertes.Version={"=offertes.Version=Max(total offertes.Version)"}>} offertes.OffAmtDLc1 )

[#5]

Correct me if im wrong, but when using the total you wil only get the sum of the highest version.

The lines represent invoices lines.

I need the sum of all the invoices there highest version. In my previous example i want the sum of all the bold lines.

Not only from those with version 2 because that is the highest version.

I'm sorry if these was not clear, I have not explained this correctly in my first post.

OffNum      OffLine     Version     Amount

     1               1               1               10

     1               2               1               15

     1               1               2               9

     1               2               2               14

     2               1               1               4

     2               2               1               5

Thank you for your help.

[#6]

I am not sure if this is what you want, but if you have OffNum as dimensions, this function will show the total of the highest version within a OffNum, so it shows:

OffNum     Sum(Amount)

     1                 23

     2                  9

If you select Expression Total in Total Mode for your expression you will get a total at the top, like this:

OffNum     Sum(Amount)

                        32

     1                 23

     2                  9

[whiteline · Accepted solution]

Ah, thats rather complex.

Try this one:

=Sum(aggr(if(offertes.Version=Max(total<offertes.OffNum> offertes.Version), Sum (offertes.OffAmtDLc1)), offertes.OffNum, OfferteKey))

[#8]

That seams to do the job, thanks. Could you explain what you've done?

Thank you

[whiteline]

Two tricks: aggr function and total qualifier with restrictions <>.

For every OfferteKey and offertes.OffNum as dimensions it summs up only those values of Sum (offertes.OffAmtDLc1) where offertes.Version=Max(total<offertes.OffNum> offertes.Version). And Max calculates for every offertes.OffNum.

If it works for you, mark the right answer. Thx.