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 |
