topic Re: Filtering Data with max in QlikView

Filtering Data with max

Tue, 11 Sep 2012 06:44:50 GMT

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

whiteline — Tue, 11 Sep 2012 07:43:55 GMT

You can use:

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

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

depending on what your dimensions are.

Re: Filtering Data with max

Tue, 11 Sep 2012 08:48:48 GMT

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

Hope you can check it out.

Re: Filtering Data with max

whiteline — Tue, 11 Sep 2012 09:29:24 GMT

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

Tue, 11 Sep 2012 09:52:53 GMT

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.

Re: Filtering Data with max

Tue, 11 Sep 2012 10:32:39 GMT

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)

1 23

2 9

Re: Filtering Data with max

whiteline — Tue, 11 Sep 2012 10:33:39 GMT

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

Tue, 11 Sep 2012 10:49:38 GMT

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

Thank you

Re: Filtering Data with max

whiteline — Tue, 11 Sep 2012 11:27:09 GMT

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.