Count of Mode in Straight Table

[Anonymous]

Hello,

I am displaying data in a straight table.  I want to display the 'Mode' (most frequent occurrence) in one column and then the count or number of times that mode value has occurred in the next column.

I have the mode, but I can't figure out how to calculate the count of the mode value... any help would be greatly appreciated.  Please see a sample data set attached.  You can see a couple of my attempts to calculate the mode within the straight table.

Thanks!

Noelle

[shiveshsingh]

Do you want to see count of mode in one column?

[Anonymous]

Hi Shivesh,

     Thanks for the reply.

     Yes, I want to determine how many times the mode value occurred, and place that count in a column in the table.

Thanks - Noelle

[shiveshsingh]

Can you share the sample data please?

[Anonymous]

For example, in the sample data set I provided, for vehicle 2017 V7 the most frequent complaint (or mode) is 'air bag' and this complaint occurred 5 times.  So this 2016 V7 row should say '5' in the Count of Mode column.

Thanks - Noelle

[Anonymous]

The data is included in the qvw file attached in the original note.

Thanks - Noelle

[shiveshsingh]

Sorry   my qlik license is over, that's why asking for sample data.

Else i'll rep tomorrow

[Anonymous]

Oh sure - here it is:

Sample_Dataset:

Load * inline

[

Model_Year, Model, NHTSA_Component, VOQ_Date

2012, V1, Air Bag, 20180101

2012, V2, Electrical, 20180201

2013, V3, Other, 20180101

2013, V3, Air Bag, 20180201

2014, V4, Steering, 20180401

2014, V1, Air Bag, 20180301

2015, V5, Other, 20180101

2015, V5, Other, 20180201

2015, V5, Other, 20180501

2015, V6, Electrical, 20180401

2016, V7, Air Bag, 20180101

2016, V7, Air Bag, 20180201

2016, V7, Air Bag, 20180301

2016, V7, Air Bag, 20180401

2016, V7, Air Bag, 20180501

2016, V7, Steering, 20180101

2016, V7, Steering, 20180501

2016, V7, Other, 20180201

2016, V7, Other, 20180401

2016, V7, Electrical, 20180501

2016, V4, Too Cool, 20180101

2016, V4, Too Cool, 20180301

];

Sample_2:

LOAD Model_Year, Model, NHTSA_Component, left(VOQ_Date, Len(VOQ_Date)-2) as New_VOQ_Date

Resident Sample_Dataset;

And here are a couple of methods i tried - neither worked.

=max(aggr(count(VOQ_Date), New_VOQ_Date))

=Mode( TOTAL Aggr( Count({<NHTSA_Component>}, Model_Year, Model))

Thanks - Noelle

[shiveshsingh]

Hi

Try this

=aggr(max(aggr(count(Model),Model_Year,NHTSA_Component)),Model_Year)

[shiveshsingh]

This?