Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Dear Jagan.
Thanks. I will try when I am back in office.
Cheers.
Joerg
Dear Jagan
I think rank function will better fit my needs.
Thank you for helping.
Cheers
Joerg
Hi Joerg,
Can I suggest you try FirstSortedValue() function.
Let me know if it works
Regards
hii
see attached file
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);
Hi
Thanks a lot. This helps me.