Max number of hits within a time range

[#1]

Hello all,

I have almost 120K records (hits) within 6 months. I need to know what day had the max count of hits.

I need this to be static (fixed value) during other calculations.

The fields I have for this:

ID_hits

num(floor("FechaHora")) as DateFLOOR

[MK_QSL]

Use below in Text Box

=Date(FirstSortedValue(DateFLOOR, -Aggr(COUNT(ID_hits),DateFLOOR))

or

=Date(FirstSortedValue(DISTINCT DateFLOOR, -Aggr(COUNT(ID_hits),DateFLOOR))

[#3]

Hello Manish, let me add this image so I can explain better.

As you can see, the graph is sorted by date and the max number of hits were obtained 02/03/15 (2,557).

The 2,557 is the value I want to display and this should be a fixed value as I want to compare by the best day in terms of traffic (hits).

Thanks for your help!

[MK_QSL]

=FirstSortedValue(DISTINCT Aggr(COUNT(ID),DateField), -Aggr(COUNT(ID),DateField))

[#5]

works perfect, 2,557 is the value I get.

last question, how do I set it as FIXED (constant)

thanks a lot!!!

[MK_QSL]

=FirstSortedValue(DISTINCT Aggr(COUNT({1}ID),DateField), -Aggr(COUNT(ID),DateField))

[MK_QSL · Accepted solution]

or

=FirstSortedValue({1}DISTINCT Aggr(COUNT({1}ID),DateField), -Aggr(COUNT({1}ID),DateField))

[maxgro]

=subfield(concat({1} aggr(only({1}DateFLOOR),DateFLOOR),'@', -aggr(count({1} ID),DateFLOOR)), '@', 1)