Set Analysis Intersection not working correctly

felcar2013 — Wed, 27 Feb 2013 13:07:24 GMT

hi,

i used this formula to calculate total number of customers by segment (A,B,C,D,E, New, Lost) in the following table, and i need to input "0" in the intersection field of the the field value "NewCustomer" and the row value "LostCustomer" (Field Names are: Segment Period 1 and Segment Period 2)

=if(sum({$<[Segment Period 1] ={NewCustomer}>*<[Segment Period 2]={OldCustomer}>} RecordCounter)>0,0,sum({$<[Segment Period 1]=, [Segment Period 2]=>} total <[Period 1], [Period 2]> RecordCounter))

i get te intersection "new customer" and "lost customer" as "0" in the table (as desired) but when i used the partial sum option of the pivot table (presentation tab), it does not calculate it, and set a "0" instead. Same for total value (total-total cell)

I used " * " to denote intersection of the value fields, but I do not understand, why i get "0" as subtotal for the "lostcustomer" and "new customer" fields and as well as for the total. Do you know why is this happening?

Header 1	Header 2	Header 3	Header 4	Header 5	Header 6	Header 7	Header 8	Header 9
				Segment Period 2
Segment Period 1		A-Customer	B-Customer	C-Customer	D-Customer	E-Customer	LostCustomer	Total
	A-Customer	100	10	20	30	40	50	250
	B-Customer	25	120	25	35	45	50	300
	C-Customer	20	40	250	40	30	10	390
	D-Customer	40	20	40	340	10	20	470
	E-Customer	60	10	50	40	550	20	730
	NewCustomer	80	50	70	30	60	0	0
	Total	325	250	455	515	735	0	0

topic Set Analysis Intersection not working correctly in QlikView

Set Analysis Intersection not working correctly