Get Max of the count of a field based on distinct values in other field

[Anonymous]

Hi All,

I am trying to get the max count of a field. For that I am using : =Max(aggr(count(Field B),Field B)) . Till now I am getting correct results. Now I have to filter this count on the basis of distinct values in Field A.

e.g.

Field A	Field B
111	Apple
111	Apple
222	Apple
222	Samsung
333	Samsung
333	LG

The desired output will be 2 since Apple is repeated 3 times and in field A I have 2 distinct values for apple.

[swuehl · Accepted solution]

Maybe like

=Count({<FieldB = {"=rank(Count(FieldB))=1"}>} DISTINCT FieldA)

[sunny_talwar]

May be this:

Count(DISTINCT {<[Field B] = {"=Count([Field B]) = Max(TOTAL Aggr(Count([Field B]), [Field B]))"}>} [Field A])

[antoniotiman]

Try

= Max(Aggr(Count(FieldB),FieldA&FieldB))

[Anonymous]

Hi Stefan,

Thanks for your reply. Now that I got the above results I have to get the Template name that has been used max no of times based on the above criteria. The desired output will be Inhouse since it is used max no of times. Also I also have to get top 3 names and count of Name field. in this case it will be Darren (2) , Mike (1).

Field A	Field B	Template	Name
111	Apple	Inhouse	Darren
111	Apple	Inhouse	Darren
222	Apple	outofstock	Mike
111	Samsung	outofstock	Raju
222	Samsung	outofstock	Raju
222	LG	Inhouse	Raju
333	LG	outofstock	Raju
333	LG	outofstock	Raju

[swuehl]

With your sample data, you can use something like

=Mode({<[Field B] = {"=rank(Count([Field B]),4)=1"}>} Template)

and

=Concat({<[Field B] = {"=rank(Count([Field B]),4)=1"}>} 

   Aggr(Only( Name) & ' ('&Count(Name)&')', [Field B], Name),', ', -Aggr( Count(Name),[Field B], Name))

In case of ties counting the template field, the mode function will return NULL, though.

[Anonymous]

Hi Stefan,

I used the expression

=Concat({<[Field B] = {"=rank(Count([Field B]),4)=1"}>} 

   Aggr(Only( Name) & ' ('&Count(Name)&')', [Field B], Name),', ', -Aggr( Count(Name),[Field B], Name))

but it is not returning the desired results.

It will basically return me Raju(5), Darren(2), Mike(1).

But what I am looking for is once the dataset is filtered for count of max value in Field B (Apple) & count of max value in Template (Inhouse), I should get top 3 values in Name.

So when I use the above expression, it is not assigning Rank on the filtered dataset rather it is applying filter on entire dataset.

I want to apply rank on filtered dataset and than take top 3 ranks.

I hope I am able to explain it.

[Anonymous]

If you will look at the above chart, Jodzis,Jeffrey M is not in the filter conditions, yet it has been assigned Rank 2.

So when I use

=(Concat
({<[Field B] = {"=rank(Count([Field B]))=1"},
Template={"=rank(Count(Template)))=1"},
Name={"=rank(Count(Name)1,1)<=3"} 
>} 

Aggr(Only(Name) & ' ('&Count(Name)&')', [Field B], [Name]))) , I will only get Eagan,Caroline Burlingame(11). But I should also get all Names with count 3 and 2 like MaDonnell,,,Turner. 

[Anonymous]

Sorry I keep on fumbling the words. The actual sentence is:

If you will look at the above chart, Jodzis,Jeffrey M is in the filter conditions, yet it has been assigned Rank 2.

So when I use