# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save \$200. Learn More
Contributor

## 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

Tags (1)
1 Solution

Accepted Solutions
Highlighted
Valued Contributor

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

5 Replies
Highlighted
Valued Contributor

Highlighted
Contributor

## 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

Highlighted
Valued Contributor

## 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

];

Highlighted
Contributor

## 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?

Highlighted
Contributor

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

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

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