Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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
Maybe like attached?
Stefan
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))
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)...
Hi,
You can replace your Max with FirstSortedValue.
Or even use Sum(aggr(firstsortedvalue(......))).
Let me know if this is helpful.
Regards,
Gabriel
Hi,
thanks everyone!
I used the option with the firstsortedvalue and it works fine!
Regards,
Rodger