how to get the field values which is repeated max no of times in a field value.

[Navars]

Hi All,

I need to get the field values which is repeated max no of times in a field value.

i have a straight table in that case,record,value three fields are there, i need to get the one record as a output which repeats more.

attached the sample file.

before that, i used the below expression to get the max value by case. 

=aggr(max({<lcl.kpi_name_dual={'MAC Level DL TPUT (Mbps)'}>}lcl.kpi_value),lcl.case_snr
) 

Thanks

Sra1..

[Stoyan_Terziev]

Hi Sralurs,

I hope I get your question right.

If you want to get for example the value of the lcl.common_modem_chipset where a value in the field lcl.common_modem_chipset is repeated maximum times:

=FirstSortedValue(lcl.common_mod_scheme,Aggr(Count(lcl.common_mod_scheme),lcl.common_mod_scheme))

I hope that helps!

Kind regards,

S.T.

[Navars]

Hi Stoyan,

Thanks for your reply.

earlier i attached a wrong file, can you please check the new attached file.

what i want is which Record count is more, that Record only i need to display as a output.

thanks,

Sra1.. 

[Stoyan_Terziev]

Hi Sra1,

You should then do this (for a text box object):

=FirstSortedValue(Record, -Aggr((Count(Record)-Rank(Record,1))/1E10, Record))

The section with the Rank is used to pick at random one of the cases with equal top occurrences (like in your data set you have few top records with highest count of 7).

Kind regards,

Stoyan

[Navars]

Hi Stoyan,

i need to show the top record in a straight table, in the example max count is 7 and same count for multiple records. i have to show all three records.

Regards,

Sra1

[Stoyan_Terziev]

Hi Sra1,

I hope I got it right this time. I think you need to preserve the table format, but filter the table to only the top-recurring values based on selection?

In case affirmative, you can do that by creating a table Chart with dimension [Record] and expression:

=if(Count(Record)=$(=Max(Aggr(Count(Record),Record))), 1, 0)

Results will look like this (for the purpose of presentation I selected 2 out of the 3 top records):

One more hint: if you want to hide the column showing the measure, you can do this from Chart -> Presentation -> Columns section -> find the right measure and set it as 'Hide Column'

I hope that helps!

Kind regards,

S.T.

[Brett_Bleess]

Sral, did Stoyan's last post get you what you needed, or are you still working on things?  If Stoyan's post worked, remember to come back to the thread and use the Accept as Solution button on the post(s) that helped you get things working as you wanted.  If you are still working on things, please leave an update.

Regards,
Brett