Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Use this: Mode - chart function ‒ 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
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
];
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?
I've found the best solution very similar to your suggestion: