Only Include Customers with Like for Like sales Current and Prior Year

mccook — Tue, 03 Aug 2021 08:05:13 GMT

Hi,

My data is kind of like below:

Area	Customer	Product	Sales 2020	Sales 2021
North	Grape Inc	Egg	2,000	6,000
North	Orange Org	Bacon	0	4,500
South	Grape Inc	Sausage	2,500	0
East	Banana Co	Sausage	7,000	4,000
North	Apple Ind	Beans	0	0

The issue I'm trying to solve in my chart/table using set analysis is something like, if a customer has like for like sales (same area/customer/product) in 2020 and 2021, they are included and show a percentage of the total sales for 2021, so the results would be something like below (no need to show the column in blue):

Area	Customer	Product	Sales 2020	Sales 2021	Include	%
North	Grape Inc	Egg	2,000	6,000	Yes	60
North	Orange Org	Bacon	0	4,500	No	-
South	Grape Inc	Sausage	2,500	0	No	-
East	Banana Co	Sausage	7,000	4,000	Yes	40
North	Apple Ind	Beans	0	0	No	-

(so 2 customers are included as they purchased different products from us, but as they purchased the same product in 2020 and 2021 they are included)

I think I've been dancing around the solution (Aggr/Total/Intersection etc), any help/advice appreciated,

Thanks,

Dean

topic Only Include Customers with Like for Like sales Current and Prior Year in QlikView

Only Include Customers with Like for Like sales Current and Prior Year