topic Re: Count of Mode in Straight Table in QlikView

Count of Mode in Straight Table

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Count of Mode in Straight Table

shiveshsingh — Sun, 18 Feb 2018 01:52:22 GMT

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

Re: Count of Mode in Straight Table

Anonymous — Sun, 18 Feb 2018 01:55:49 GMT

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

Re: Count of Mode in Straight Table

shiveshsingh — Sun, 18 Feb 2018 02:00:29 GMT

Can you share the sample data please?

Re: Count of Mode in Straight Table

Anonymous — Sun, 18 Feb 2018 02:01:44 GMT

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

Re: Count of Mode in Straight Table

Anonymous — Sun, 18 Feb 2018 02:02:36 GMT

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

Thanks - Noelle

Re: Count of Mode in Straight Table

shiveshsingh — Sun, 18 Feb 2018 02:12:55 GMT

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

Else i'll rep tomorrow

Re: Count of Mode in Straight Table

Anonymous — Sun, 18 Feb 2018 02:19:22 GMT

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

Re: Count of Mode in Straight Table

shiveshsingh — Sun, 18 Feb 2018 02:52:59 GMT

Try this

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

Re: Count of Mode in Straight Table

shiveshsingh — Sun, 18 Feb 2018 02:54:27 GMT

This?

Re: Count of Mode in Straight Table

Anonymous — Sun, 18 Feb 2018 16:56:29 GMT

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

Re: Count of Mode in Straight Table

shiveshsingh — Mon, 19 Feb 2018 07:25:08 GMT

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

Re: Count of Mode in Straight Table

Anonymous — Mon, 19 Feb 2018 14:13:14 GMT

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