Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
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
tomasz_tru
Valued Contributor

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

5 Replies
tomasz_tru
Valued Contributor

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

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

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 

];

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?

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

Community Browser