Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, I'm almost new in Qlik world... this is a Cross Selling problem.
Let's say we have the following table, with a monthly based Invoices table; the invoice detail is at ProductGroup.
The table contains data from current year (2013) and previous year (2012).
CUSTOMER | YEARMONTH | PRODUCTGROUP | INVOICED |
---|---|---|---|
AAA | 201206 | APPLE | 100 |
AAA | 201209 | APPLE | 100 |
AAA | 201303 | APPLE | 100 |
AAA | 201304 | KIWI | 120 |
AAA | 201307 | APPLE | 130 |
AAA | 201308 | ORANGE | 100 |
BBB | 201201 | APPLE | 50 |
BBB | 201203 | ORANGE | 40 |
BBB | 201209 | PEACH | 60 |
BBB | 201303 | APPLE | 20 |
BBB | 201305 | KIWI | 70 |
BBB | 201306 | PEACH | 90 |
I need to build a Cross Selling table, where each Customer is measured with two indicators, GROUP_IN and GROUP_OUT in Current Year, respect to the Previous Year.
GROUP_IN is the number of New Product groups invoiced in the Current Year (Number of Product Groups introduced for Customer in the current year).
GROUP_OUT is the number of "lost" Product groups in the Current Year (Number of Product Groups invoiced last year, lost in the current year)
So, the CROSSELLING table would be:
CUSTOMER | GROUP_IN | GROUP_OUT |
---|---|---|
AAA | 2 | 0 |
BBB | 1 | 1 |
Any indications will be appreciated!
Thanks,
Emilio.
See attached example.
Thanks Mr. Wassenaar!