topic Re: find highest value in QlikView

find highest value

omyahamburg — Wed, 16 Jan 2013 07:32:05 GMT

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

Re: find highest value

jagannalla — Wed, 16 Jan 2013 07:45:28 GMT

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

Re: find highest value

omyahamburg — Wed, 16 Jan 2013 08:26:18 GMT

Dear Jagan.

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

Cheers.

Joerg

Re: find highest value

omyahamburg — Mon, 21 Jan 2013 07:22:10 GMT

Dear Jagan

I think rank function will better fit my needs.

Thank you for helping.

Cheers

Joerg

Re: find highest value

Gabriel — Mon, 21 Jan 2013 10:05:31 GMT

Hi Joerg,

Can I suggest you try FirstSortedValue() function.

Let me know if it works

Regards

Re: find highest value

er_mohit — Mon, 21 Jan 2013 11:24:18 GMT

hii

see attached file

Re: find highest value

Mon, 21 Jan 2013 11:34:45 GMT

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

Re: find highest value

omyahamburg — Mon, 21 Jan 2013 11:47:56 GMT

Thanks a lot. This helps me.