Qlik Community

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
bi_analysts_2
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):

dimensionvalue
a2
b1
c2
a5
c2
b3
a4
a2
c2
c3
b3

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

  

dimensionmost common value
a2
b3
c2

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

tnx,

Tal

Tags (1)
1 Solution

Accepted Solutions
Highlighted
tomasz_tru
Valued Contributor

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

5 Replies
Highlighted
tomasz_tru
Valued Contributor

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

Highlighted
bi_analysts_2
Contributor

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

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

  

datehouraccountsales
8/2/20171xx$20
8/2/20172xx$25
8/2/20173xx$18
8/2/20171yy$30
8/2/20172yy$12
8/2/20173yy$8
8/3/20171xx$13
8/3/20172xx$7
8/3/20173xx$22
8/3/20171yy$3
8/3/20172yy$17
8/3/20173yy$10

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

 

accounthour
       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
tomasz_tru
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;

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 

];

Highlighted
bi_analysts_2
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
bi_analysts_2
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