Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Ah, thats rather complex.
Try this one:
=Sum(aggr(if(offertes.Version=Max(total<offertes.OffNum> offertes.Version), Sum (offertes.OffAmtDLc1)), offertes.OffNum, OfferteKey))
You can use:
=Sum({$<Version={"=Version=Max(Version)"}>} Amount)
or
=Sum({$<Version={"=Version=Max(total Version)"}>} Amount)
depending on what your dimensions are.
Can't get it to work. I've added a example with your answer.
Hope you can check it out.
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 )
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.
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
Ah, thats rather complex.
Try this one:
=Sum(aggr(if(offertes.Version=Max(total<offertes.OffNum> offertes.Version), Sum (offertes.OffAmtDLc1)), offertes.OffNum, OfferteKey))
That seams to do the job, thanks. Could you explain what you've done?
Thank you
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.