topic Re: Sum of highest values in QlikView

Sum of highest values

Sat, 23 Feb 2013 22:08:21 GMT

Hello,

i'm trying to make a sum from the two highest values of a week. (or the two highest values in two weeks)

With the function max(), i don't get the right answer, because in one week there can be more the same values.

The max function with rank 2 doesn't show same values.

Is there something like a NODISTINCT function to get the right output.

See the example.

The sum for week 49 would be 40. And if i want it for two weeks, the sum for week 48 and 49 would be also 40.

An idea?

Regards,

Rodger

Re: Sum of highest values

phaneendra_kunc — Sun, 24 Feb 2013 09:51:07 GMT

See attached..

Incase if you cant open the attached..

Your Data

WeekGetal:

LOAD Week, Dag, Getal, if(Getal=peek(Getal),Peek(FLAG)+1,1) as FLAG Inline [

Week, Dag, Getal

44, 1, 10

44, 2, 11

44, 3, 12

45, 4, 1

45, 5, 2

45, 6, 3

46, 7, 13

46, 8, 14

46, 9, 15

47, 10, 4

47, 11, 5

47, 12, 6

48, 13, 16

48, 14, 18

48, 15, 18

49, 16, 20

49, 17, 20

];

Front end Expression: max(Getal) *max(FLAG)

Re: Sum of highest values

Sun, 24 Feb 2013 21:15:51 GMT

Hello,

thanks for your answer, but i'm not getting the result I want.

What I want is a sum from the first two values, something like rangemax from the two highest results.

When I use peek, the second highest rank gets a FLAG 1, so I can't show the second highest rank. And an other thing is I can't sort the data the way I want.

I have more the same numbers in a week, somthing like 33, 33, 32, 32. The second value is 33 but also 32 has flag 2, that's why I can't use FLAG=2.

Hopefully you have an other option?

Regards,

Rodger

Re: Sum of highest values

swuehl — Sun, 24 Feb 2013 21:32:45 GMT

Maybe like attached?

Stefan

Re: Sum of highest values

phaneendra_kunc — Sun, 24 Feb 2013 21:52:34 GMT

This is what i understand...

Lets say if you have data like this

Week	Getal

48	18
48	16
49	20
49	20
41	11

Desired result:

week	expression
41	11
48	18+16 = 34
49	20+20 = 40

Expression used: =if(max(FLAG)=2, Max(Getal)*Max(FLAG),Max(Getal)+Max(Getal,2))

Re: Sum of highest values

phaneendra_kunc — Sun, 24 Feb 2013 21:58:40 GMT

Stefan

I thought of First sorted Value too..But couldn't make it to work...

I like yours better ....But can we get it to work without second LOAD or extra columns (Like FLAG, WeekDagNr..etc)...

Re: Sum of highest values

Gabriel — Sun, 24 Feb 2013 22:11:48 GMT

Hi,

You can replace your Max with FirstSortedValue.

Or even use Sum(aggr(firstsortedvalue(......))).

Let me know if this is helpful.

Regards,

Gabriel

Re: Sum of highest values

Wed, 06 Mar 2013 14:23:01 GMT

Hi,

thanks everyone!

I used the option with the firstsortedvalue and it works fine!

Regards,

Rodger