Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to get Count for Max(Value)
This is my dataset
ID | No | Value |
1 | 1 | 11 |
1 | 2 | 30 |
1 | 3 | 30 |
2 | 1 | 40 |
2 | 2 | 40 |
2 | 3 | 10 |
3 | 1 | 10 |
4 | 1 | 10 |
and I want the result be like this
ID | Max Value | Count Max Value |
1 | 30 | 2 |
2 | 40 | 2 |
3 | 10 | 1 |
4 | 10 | 1 |
I try to use this count({<Value = {'$(=max(Value))'}>} No)
but I got 0 in the result
ID | Max Value | Count Max Value |
1 | 30 | 2 |
2 | 40 | 0 |
3 | 10 | 0 |
4 | 10 | 0 |
Expression evaluated show only 30 for the Max (value)
count({<Value = {'30'}>} No)
Is it possible to get the first row number of the Max(Value)?
Thank you in advance.
Hi @nudjaree1
May be,try like below
Sum(Aggr(if(Value = Max(Total<ID>Value), 1, 0) , ID, No))
You may try: count({<Value = {"=Value=max(Value)"}>} No)
whereby it's not a real set analysis anymore else it worked like an if-loop.
- Marcus
Hi Marcus,
Thank you for the answer but I got 8 for every ID
Max Value | Max Value | |
1 | 30 | 8 |
2 | 40 | 8 |
3 | 10 | 8 |
4 | 10 | 8 |
Regards
Nudjaree
Hi @nudjaree1
May be,try like below
Sum(Aggr(if(Value = Max(Total<ID>Value), 1, 0) , ID, No))
Thank you MayiVahanan. It works 🙂
Is it possible to get the first NO of Sum(Aggr(if(Value = Max(Total<ID>Value), 1, 0) , ID, No))
ID MAx Count No
1 30 3 2
2 40 3 1
3 10 1 1
4 10 1 1