Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Count of Mode in Straight Table

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

12 Replies
Anonymous
Not applicable
Author

Thanks for the suggestion, Shivesh, but unfortunately that is not providing the answer I expected.

In fact, I now see that even my Mode function (=mode(NHTSA_Component)) is not displaying the right answers.  2016 V7 should have a Mode of 'Air Bag' since that value occurs the most times (5), and it's displaying as '-'.

Any other suggestions for determining the Mode or counting the occurrence of the (Mode)?

      

Rank - Most ComplaintsModel_YearModelCount ComplaintsMode=aggr(max(aggr(count(Model),Model_Year,NHTSA_Component)),Model_Year)
1.52012V11Air Bag1
1.52012V21Electrical-
12013V32-1
1.52014V11Air Bag-
1.52014V41Steering1
12015V53Other1
22015V61Electrical-
12016V710--
22016V43-2

Thanks - Noelle

shiveshsingh
Master
Master

Hi

Please remove synthetic key also from your model.

drop table Sample_Dataset

and in place of VOQ_Date put New_VOQ_Date in charts

Rank - Most Complaints Model_Year Model Count Complaints Mode Trend Chart aggr(max(aggr(count(Model),Model_Year,NHTSA_Component)),Model_Year)
1-22012V11Air Bag<<image>>1
1-22012V21Electrical<<image>> 
12013V32 <<image>>1
1-22014V11Air Bag<<image>> 
1-22014V41Steering<<image>>1
12015V53Other<<image>>3
22015V61Electrical<<image>> 
22016V42Too Cool<<image>> 
12016V710Air Bag<<image>>5
Anonymous
Not applicable
Author

Hi Shivesh,

Thanks for the suggestions.  After trying a few things i got this to work:

1.  Added new field in script, Mode_NC:

TableJoin:

INNER JOIN (Table1)

Table2:

LOAD Model_Year, Model, Mode(NHTSA_Component) as Mode_NC

Resident Table1

Group By Model_Year, Model;

2. Added this expression to my straight table to calculate the % representation of the Mode complaint:

= aggr ( sum( if ( [Mode_NC] = [NHTSA_Component], 1, 0)), Model_Year, Model) / aggr (count ([NHTSA_Component] ), Model_Year , Model )

Thanks!

Noelle