Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sum of highest values

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Maybe like attached?

Stefan

View solution in original post

7 Replies
phaneendra_kunc
Partner - Specialist III
Partner - Specialist III

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)

Not applicable
Author

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

swuehl
MVP
MVP

Maybe like attached?

Stefan

phaneendra_kunc
Partner - Specialist III
Partner - Specialist III

This is what i understand...

Lets say if you have data like this

WeekGetal

4818
4816
4920
4920
4111

Desired result:

weekexpression
4111
4818+16 = 34
4920+20 = 40

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

phaneendra_kunc
Partner - Specialist III
Partner - Specialist III

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)...

Gabriel
Partner - Specialist III
Partner - Specialist III

Hi,

You can replace your Max with FirstSortedValue.

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

Let me know if this is helpful.

Regards,

Gabriel

Not applicable
Author

Hi,

thanks everyone!

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

Regards,

Rodger