Function to find the most common value per dimension (qlik sense)

[Anonymous]

hi,

let's say for instance we have the data set below (loaded to Qlik):

dimension	value
a	2
b	1
c	2
a	5
c	2
b	3
a	4
a	2
c	2
c	3
b	3

i would like to create a chart and receive the output as following:

dimension	most common value
a	2
b	3
c	2

is there a function or a technique to receive such a result?

tnx,

Tal

[tomasz_tru · Accepted solution]

Use this: Mode - chart function ‒ Qlik Sense

[Anonymous]

follow up question - now let's take the data set below:

date	hour	account	sales
8/2/2017	1	xx	$20
8/2/2017	2	xx	$25
8/2/2017	3	xx	$18
8/2/2017	1	yy	$30
8/2/2017	2	yy	$12
8/2/2017	3	yy	$8
8/3/2017	1	xx	$13
8/3/2017	2	xx	$7
8/3/2017	3	xx	$22
8/3/2017	1	yy	$3
8/3/2017	2	yy	$17
8/3/2017	3	yy	$10

i would like to create a chart and receive for each account the most succeeding sales hour:

account	hour
xx	3
yy	1

account xx most succeeding sales hour is 3, because $18+$22=$40, more than:

1: $20+$13=$33

2: $25+$7=$32

have any technique or function to receive such an output?

tnx!

Tal

[tomasz_tru]

Here you go:

OutputTable:

Load account, FirstSortedValue(hour ,-SumSales) AS BestHour GROUP by account;

Load account, hour, sum(sales_num) AS SumSales GROUP BY account, hour;

Load date, hour, account, PurgeChar(sales,'$') AS sales_num;

Load * inline [

date, hour, account, sales

8/2/2017, 1, xx, $20 

8/2/2017, 2, xx, $25 

8/2/2017, 3, xx, $18 

8/2/2017, 1, yy, $30 

8/2/2017, 2, yy, $12 

8/2/2017, 3, yy, $8 

8/3/2017, 1, xx, $13 

8/3/2017, 2, xx, $7 

8/3/2017, 3, xx, $22 

8/3/2017, 1, yy, $3 

8/3/2017, 2, yy, $17 

8/3/2017, 3, yy, $10 

];

[Anonymous]

first of all thank you very much for your help!

second, i would like it to be dynamic, therefore i rather to use a function while creating the required chart - is that possible?

[Anonymous]

I've found the best solution very similar to your suggestion:

https://community.qlik.com/message/1318423#1318423