topic Most frequent table value for a dimension in QlikView

Most frequent table value for a dimension

Fri, 25 May 2012 12:30:14 GMT

Hi all,

I have a table like:

Customer | Item | item category | price | qty

I would like to get as result something like

Cutomer (as a dimension) | Turnover (as qty * price) | most frequent ordered category

The result is provided through a table drawn as a chart.

and I don't have any idea on how to turn the expression to get that "most frequent ordered category"

Any idea?

Thanks in advance for your usual support

Most frequent table value for a dimension

Fri, 25 May 2012 12:32:58 GMT

I think i found the answer mode()

Most frequent table value for a dimension

Mon, 28 May 2012 07:12:33 GMT

The mode() fonction seems not to work properly, in a specific case I have an item_category that is equal to null and the customer order more than 10 items (on a total of 15) from a category "A" and QV returns "null" as the result of mode()

Most frequent table value for a dimension

Nicole-Smith — Wed, 30 May 2012 12:49:45 GMT

Try something like this:

if(item_category <> NULL(), mode(item_category))

Most frequent table value for a dimension

sivarajs — Wed, 30 May 2012 13:37:57 GMT

do something like aggr(max(category),customer)

Re: Most frequent table value for a dimension

Thu, 31 May 2012 06:02:18 GMT

Thank you both but unfortunatly it does not work.

I extracted 3 cases where I have a wrong answer with the mode() function:

Customer	Order Id	Item	Item Categrory	Price
CUST A	72310	Product 1	-	150,11
CUST A	72310	Product 2	-	145,29
CUST A	73910	Product 3	CAT B	48,16
CUST A	73910	Product 4	CAT D	165,13
CUST A	73910	Product 5	CAT E	21,58
CUST A	73910	Product 6	CAT B	36,14
CUST A	73910	Product 7	CAT B	86,19
CUST A	73910	Product 8	CAT A	71,24
CUST A	73910	Product 9	CAT B	18,97
CUST A	88280	Product 10	CAT D	229,14
CUST A	88280	Product 11	CAT D	130,36
CUST B	89974	Product 12	CAT B	9,86
CUST B	89974	Product 13	CAT B	13,96
CUST B	89974	Product 14	CAT B	5,64
CUST B	89974	Product 15	CAT B	12,68
CUST B	89974	Product 16	CAT B	21,14
CUST B	89974	Product 17	CAT B	33,72
CUST B	89974	Product 18	CAT B	8,22
CUST B	89974	Product 19	CAT C	47,53
CUST B	89974	Product 20	CAT C	59
CUST B	89974	Product 21	CAT B	17,52
CUST B	89974	Product 22	CAT C	48,72
CUST B	89974	Product 23	CAT C	62,61
CUST B	89974	Product 24	CAT C	45,37
CUST B	89974	Product 25	CAT B	11,74
CUST B	90854	Product 26	CAT C	82,8
CUST B	90854	Product 27	CAT C	75,61
CUST B	90854	Product 28	CAT C	86,4
CUST B	90854	Product 29	CAT C	93,61
CUST C	89380	Product 30	CAT B	12,62
CUST C	89380	Product 31	CAT B	43,13
CUST C	89380	Product 32	CAT A	20,32
CUST C	89380	Product 33	CAT B	20,27
CUST C	89380	Product 34	CAT B	19,68
CUST C	89380	Product 35	CAT B	6,79
CUST C	89380	Product 36	CAT B	12,18
CUST C	89380	Product 37	CAT B	10,79
CUST C	89380	Product 38	CAT B	23,47
CUST C	89380	Product 39	CAT B	7,39
CUST C	89380	Product 40	CAT B	12,18
CUST C	89380	Product 41	CAT B	13,49
CUST C	89380	Product 42	CAT A	20,37
CUST C	89380	Product 43	-	23,99
CUST C	89380	Product 44	CAT B	34,89
CUST C	89380	Product 45	CAT A	62,81
CUST C	89380	Product 46	CAT B	17,52
CUST C	89380	Product 47	CAT A	10,89

For Cust B i can understand as frequence for CAT B and CAT C is equal to 9

But for Cust C, I would have Cat B (freq=13 vs 4 or 1) and for Cust A the result should be Cat B (freq 4 vs 1,1,3,2)

Most frequent table value for a dimension

Nicole-Smith — Thu, 31 May 2012 12:29:20 GMT

Can your post your .qvw and some data so that we can see it?

Most frequent table value for a dimension

ToniKautto — Sat, 02 Jun 2012 15:16:46 GMT

If more than one value is equally commonly occurring NULL is returned.