topic Re: Only sum expression where the highest version in QlikView

Only sum expression where the highest version

Wed, 19 Oct 2011 14:07:06 GMT

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

Only sum expression where the highest version

swuehl — Wed, 19 Oct 2011 14:49:00 GMT

Try an expression with an advanced aggregation like

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

Hope this helps,

Stefan

Only sum expression where the highest version

Thu, 20 Oct 2011 10:20:34 GMT

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.

Re: Only sum expression where the highest version

Miguel_Angel_Baeyens — Thu, 20 Oct 2011 11:14:15 GMT

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

Re: Only sum expression where the highest version

Thu, 20 Oct 2011 11:59:50 GMT

Thanks a lot. That solved the problem.