Cross Selling Problem

[ebrambilla]

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.

[Gysbert_Wassenaar · Accepted solution]

See attached example.

[ebrambilla]

Thanks Mr. Wassenaar!