Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts ,
I'm a begginer on QV and I'm looking for a solution about a basket customer analysis like this
I have quantities sold of 4 items (or Item Groups) for several customers (or Cust Groups):
Data set
Sum of qty | Item A | Item B | Item C | Item D |
Customer 1 | 50 | 42 | 5 | 0 |
Customer 2 | 120 | 0 | 40 | 0 |
Customer 3 | 0 | 65 | 0 | 32 |
Customer 4 | 0 | 10 | 40 | 121 |
Customer 5 | 0 | 0 | 50 | 130 |
Customer 6 | 0 | 5 | 1 | 0 |
and the target result should be a cross table built as sum qty of both items if and only if they are sold together
Cross table
summary of both items sold | Item A | Item B | Item C | Item D |
Item A | 0 | 92 | 215 | 0 |
Item B | 92 | 0 | 103 | 228 |
Item C | 215 | 103 | 0 | 341 |
Item D | 0 | 228 | 341 | 0 |
that is
Item A+B | 92 |
Item A+C | 215 |
Item A+D | 0 |
Item B+C | 103 |
Item B+D | 228 |
Item C+D | 341 |
How can build a matrix like this ?
Thanks in advance
See attached example
A slightly different approach:
TMP:
CROSSTABLE (Item, Value,2) LOAD recno() as ID, * INLINE [
Customer, Item A, Item B, Item C, Item D
Customer 1, 50, 42, 5, 0
Customer 2, 120, 0, 40, 0
Customer 3, 0, 65, 0, 32
Customer 4, 0, 10, 40, 121
Customer 5, 0, 0 ,50 ,130
Customer 6 ,0, 5 ,1, 0
];
BASKET:
NOCONCATENATE LOAD * Resident TMP where Value >0;
LOAD ID, Item as Item2, Value as Value2 Resident TMP where Value >0;
drop table TMP;
Then create a pivot with dimensions Item and Item2 and as expresssion:
=if (Item <> Item2,rangesum(sum(Value),sum(Value2)))
Regards,
Stefan
Thanks a lot
Gysberth & Swuehl
for your suggestions !!!
Regards