topic Max repeats by day in QlikView

Max repeats by day

Wed, 14 Oct 2009 21:39:31 GMT

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.

Max repeats by day

Wed, 14 Oct 2009 22:03:47 GMT

2 questions:

Will the account numbers be in a single string field?

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

Max repeats by day

Wed, 14 Oct 2009 22:08:59 GMT

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.

Max repeats by day

Wed, 14 Oct 2009 22:17:13 GMT

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

Max repeats by day

Wed, 14 Oct 2009 22:46:12 GMT

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.

Max repeats by day

rwunderlich — Wed, 14 Oct 2009 22:59:05 GMT

Use the expression:

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

See the attached extension of Dragonauta's example.

-Rob

Max repeats by day

Wed, 14 Oct 2009 23:11:12 GMT

or you could try something like


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

if you only want to display the top value

Max repeats by day

Wed, 14 Oct 2009 23:35:18 GMT

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