find highest value

[omyahamburg]

Hi all

Seems my head is not the best today.

I am trying to find the highest value in a table. In this case it is the value in month 10.

ID	Month	Value
Item	01	7.279.389
Item	02	7.621.106
Item	03	7.928.659
Item	04	8.374.085
Item	05	7.582.951
Item	06	8.461.163
Item	07	8.800.933
Item	08	8.578.971
Item	09	8.505.260
Item	10	9.313.773
Item	11	9.109.731
Item	12	8.673.098

The desired result should look like this:

ID	Month	Value
Item	10	9.313.773

I tried with max, but failed.

Can somebody give me an idea for a formula ?

Thanks in advance.

Joerg

[jagannalla]

Hi,

Take Dimension as ID and expression as =firstsortedvalue ( Value, -Month) 

Otherwise go like this Take Dim as ID, Month and expression as =Sum({<Month={$(=Max(Month))}>}Value)

Cheers!!

Jagan

[omyahamburg]

Dear Jagan.

Thanks. I will try when I am back in office.

Cheers.

Joerg

[omyahamburg]

Dear Jagan

I think rank function will better fit my needs.

Thank you for helping.

Cheers

Joerg

[Gabriel]

Hi Joerg,

Can I suggest you try FirstSortedValue() function.

Let me know if it works

Regards

[er_mohit]

hii

see attached file

[#7]

    hi

try it

LOAD

only(id),

max(value) as maxvalue,

FirstSortedValue(mon,-value) as monthno.;

LOAD id,

         mon,

         value

FROM

C:\test41.xlsx

(ooxml, embedded labels, table is Sheet1);

[omyahamburg]

Hi

Thanks a lot. This helps me.