Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tomovangel
Partner - Specialist
Partner - Specialist

Aggr or max ?

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))Screenshot_1.png
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

17 Replies
caio_caminoski
Creator
Creator

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
Partner - Specialist
Partner - Specialist
Author

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

tomovangel
Partner - Specialist
Partner - Specialist
Author

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
Creator
Creator

Can you try this expression:

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

caio_caminoski
Creator
Creator

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

caio_caminoski
Creator
Creator

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

tomovangel
Partner - Specialist
Partner - Specialist
Author

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

caio_caminoski
Creator
Creator

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)