Aggr or max ?

[tomovangel]

Hello guys, I am relatively new to Qlik Sense, and I have been struggling this past few hours with the following problem:

I have a table from Qlik Connectors which gives me sub_name( hour of the day) and value( number of online fans( from my FB page))
I want to get the maximum value, and the corresponding hour to it,
I tried using Aggr(Max(value),sub_name) but it doesn't work

My end result should be
Max online fans are at 11 - 285.

I have thought of the peek function, but I am not sure

Any help will be highly appreciated.

-Angel

[caio_caminoski]

Maybe this thread could help you: https://community.qlik.com/thread/171081

Also in your situation you have two rows with the same max value of 285. swuehl gave me a good tip there.

Regards,

Caio

[tomovangel]

if i Use Max(Aggr(FirstSortedValue(DISTINCT sub_name,-value),end_date_pacific))
It returns 23,

[tomovangel]

Yeah, in your case you are using a function which is Count, In my case i got only raw data
1 - 220
2 - 225
etc.
and for each day they are different ( some days not of course but, you get the idea)
thanks for the thread i will look into it with more detail

-Angel

[caio_caminoski]

Can you try this expression:

=FirstSortedValue( sub_name, aggr(-(value+sub_name/10000), sub_name)) & - & max(value)

[caio_caminoski]

=FirstSortedValue( sub_name, aggr(-(value+sub_name/10000), sub_name)) &' - '& max(value)

[caio_caminoski]

Oh, sorry, i didn't see you got a correct answer up there!

[tomovangel]

Yea, yours givess me only - 334 ( and misses the hour, try the above expression in your case and see if it works) )

[caio_caminoski]

For me the function I passed you worked fine, but I guess you have a more complex table than the one I tried and eventually the values repeat, which does not permit it to "aggregate" as unique values.

If you woud add a "max" it would probably bring you the correct answer, such as:

=FirstSortedValue( sub_name, aggr(-(Max(Value+sub_name/10000)), sub_name)) &' - '& max(Value)

Just one thing I noticed about the answer marked as correct is that it may bring you the wrong result if the sub_name is between 0 to 9, for exemple, I tested with the following inline and the result was a bit misleading

LOAD * INLINE [

    sub_name, Value

    0, 120

    1, 160

    2, 200

    8, 295

    9, 10

    10, 200

    11, 285

    12, 100

];

You can adjust that doing

=FirstSortedValue(DISTINCT sub_name,-Value&Num(sub_name,'00'))&'-'&max(Value)