Max repeats by day

[#1]

Hi,

I am wanting to report on the maximum number of repeated account numbers by day in a set of data.

The data will look like:

13/10/2009: 4,5,6,7,7,7,6,8

14/10/2009: 4,5,5,6,7,7,8

15/10/2009: 4,4,4,4,7,8,6,8

This should give the values of 3, 2 and 4 on the 3 days.

I thought the code would be something along the lines of MAX(count(distinct accountno)) or MAX(distinct accountno), but this does not work.

Thanks in advance.

[#2]

2 questions:

Will the account numbers be in a single string field?

According to your examples you're only counting consecutive repeated account numbers?

[#3]

Hi, The data will be in the same column in multiple rows. These will not be sorted by the account number, so it will be the maximum number of occurances of any account number grouped by day, regardless of the order.

[#4]

I'm not sure if attached example is exactly what you're looking for, but with some minor tweaks it should get you there.

[#5]

It looks along the right lines. The correct cells are highlighted in green (not sure how you've done that), but I also need to get rid of the rest of the table, leaving just the highlighted values and the dates.

[rwunderlich · Accepted solution]

Use the expression:

=max(aggr(count(AccountNumbers), AccountNumbers, Date))

See the attached extension of Dragonauta's example.

-Rob

[#7]

or you could try something like

if(Rank(Count(AccountNumbers))=1,Count(AccountNumbers))

if you only want to display the top value

[#8]

Thanks for both of your help. This is spot on.