Only sum expression where the highest version

[#1]

Hello,

I have attached a file. In this Pivottable I have three projects with versions and hours for each version.

I want to collapse the table to show only project and Sum(values) but I ONLY want to have the Sum(values) for the highest version. For instance for the project KL008 the result should be 1174 and not 2652. How can I achieve that?

UPDATE:

I updated the file because I have an another issue. In the old uploaded file, every project had only one row version and value. Thus, the idea from swuehl worked. However in my productive database, project has many rows, like this:

project - version - value

KL008 - 4 - 242

KL008 - 4 - 235

KL008 - 4 - 275

KL008 - 5 - 391

KL008 - 5 - 390

KL008 - 5 - 393

So, in pivot table the expression sum(values) show for KL008 and version 5 the result "1174" which is correct. How to show the cumulated result for the highest version???

Thanks for your help.

Kind regards

[swuehl]

Try an expression with an advanced aggregation like

=sum(aggr(if(version=max(total<project> version),values),project,version))

Hope this helps,

Stefan

[#3]

Hi swuehl, it worked for the uploaded file. Unfortunately my database had an another structure which your recommendation did not work. I updated my orginal discussion. Thanks for your help.

[Miguel_Angel_Baeyens · Accepted solution]

Hi,

Try the following expression instead

Sum(If(Version = Aggr(NODISTINCT Max(TOTAL <Project> Version), Project), Value))

When the Max() returns more than one value and you want to take into account all the values, you need to add the NODISTINCT keyword to the Aggr() function.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#5]

Thanks a lot. That solved the problem.