Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I think i found the answer mode()
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()
Try something like this:
if(item_category <> NULL(), mode(item_category))
do something like aggr(max(category),customer)
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)
Can your post your .qvw and some data so that we can see it?
If more than one value is equally commonly occurring NULL is returned.