5 Replies Latest reply: Aug 3, 2017 7:56 AM by Tal Sibony

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

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

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

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

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

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;

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

];

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

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?