Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 Complaints | Model_Year | Model | Count Complaints | Mode | =aggr(max(aggr(count(Model),Model_Year,NHTSA_Component)),Model_Year) |
1.5 | 2012 | V1 | 1 | Air Bag | 1 |
1.5 | 2012 | V2 | 1 | Electrical | - |
1 | 2013 | V3 | 2 | - | 1 |
1.5 | 2014 | V1 | 1 | Air Bag | - |
1.5 | 2014 | V4 | 1 | Steering | 1 |
1 | 2015 | V5 | 3 | Other | 1 |
2 | 2015 | V6 | 1 | Electrical | - |
1 | 2016 | V7 | 10 | - | - |
2 | 2016 | V4 | 3 | - | 2 |
Thanks - Noelle
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-2 | 2012 | V1 | 1 | Air Bag | <<image>> | 1 |
1-2 | 2012 | V2 | 1 | Electrical | <<image>> | |
1 | 2013 | V3 | 2 | <<image>> | 1 | |
1-2 | 2014 | V1 | 1 | Air Bag | <<image>> | |
1-2 | 2014 | V4 | 1 | Steering | <<image>> | 1 |
1 | 2015 | V5 | 3 | Other | <<image>> | 3 |
2 | 2015 | V6 | 1 | Electrical | <<image>> | |
2 | 2016 | V4 | 2 | Too Cool | <<image>> | |
1 | 2016 | V7 | 10 | Air Bag | <<image>> | 5 |
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