8 Replies Latest reply: Sep 11, 2012 7:27 AM by whiteline _

# Filtering Data with max

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

• ###### Re: Filtering Data with max

You can use:

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

or

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

depending on what your dimensions are.

• ###### Re: Filtering Data with max

Hope you can check it out.

• ###### Re: Filtering Data with max

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 )

• ###### Re: Filtering Data with max

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

• ###### Re: Filtering Data with max

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

• ###### Re: Filtering Data with max

Ah, thats rather complex.

Try this one:

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

• ###### Re: Filtering Data with max

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

Thank you

• ###### Re: Filtering Data with max

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.