Help with Set Analysis

[sangeess21]

Hi,

I would like to show new customers in a pivot table. A new customer is one who has not purchased anything(Direct/Indirect) from the company in the past. The concept I use is, if the customer does not have any SP value in the past and does have a SP Value this year, then that customer is a new customer.

If the below example is considered,

		Year	2015	2016	2017
Direct/Indirect Customer	Service Class	SP Name	SP Value	SP Value	SP Value
Direct	ABC	Customer A	$0.00	$0.00	$2000.00
Direct	ABC	Customer B	$0.00	$0.00	$4000.00
Direct	XYZ	Customer B	$0.00	$0.00	$6000.00
Direct	XYZ	Customer A	$250.00	$350.00	$0.00
Direct	Total		$250.00	$350.00	$12,000.00
Indirect	XYZ	Customer A	$0.00	$0.00	$3,500.00
Indirect	Total	Customer B	-	-	$3,500.00

I would like to get a pivot table like this.

Direct/Indirect Customer	Service Class	SP Name	SP Value
Direct	ABC	Customer B	$4000.00
Direct	XYZ	Customer B	$6000.00

But when use the below set analysis,

if(( if ([Direct/Indirect Customer] = 'Direct', sum({<ID={'1','2','3'},Year={"$(=year(today(1)))"}>}Amount), sum({<ID-={'1','2','3'},Year={"$(=year(today(1)))"}>}Amount)) <>0 and

if ([Direct/Indirect Customer] = 'Direct', sum({<ID={'1','2','3'},Year-={"$(=year(today(1)))"}>}Amount), sum({<ID-={'1','2','3'},Year-={"$(=year(today(1)))"}>}Amount)) = 0,

if ([Direct/Indirect Customer]='Direct', sum({<ID={'1','2','3'},Year={"$(=year(today(1)))"}>}Amount), sum({<ID-={'1','2','3'},Year={"$(=year(today(1)))"}>}Amount)))

I get this result where Customer A is not a new customer as it has purchased directly from the company in 2015 and 2016.

Direct/Indirect Customer	Service Class	SP Name	SP Value
Direct	ABC	Customer B	$4000.00
Direct	ABC	Customer A	$2000.00
Direct	XYZ	Customer B	$6000.00
Indirect	XYZ	Customer A	$3500.00

If I use the below expression, I do not get any result.

if((if([Direct/Indirect Customer] = 'Direct',sum({<ID={'1','2','3'},Year={"$(=year(today(1)))"}>}Amount),sum({<ID-={'1','2','3'},Year={"$(=year(today(1)))"}>}Amount))<>0 and

if([Direct/Indirect Customer] = 'Direct',sum({<ID={'1','2','3'},Year-={"$(=year(today(1)))"}>}Amount))=0 and

if([Direct/Indirect Customer] = 'Indirect',sum({<ID-={'1','2','3'},Year-={"$(=year(today(1)))"}>}Amount))=0,

if([Direct/Indirect Customer]='Direct', sum({<ID={'1','2','3'},Year={"$(=year(today(1)))"}>}Amount),sum({<ID-={'1','2','3'},Year={"$(=year(today(1)))"}>}Amount)))

Am I missing anything here? Any help is greatly appreciated!

Thanks!

[Anil_Babu_Samineni]

Would you like to do Cross Table?

[sunny_talwar]

What is ID field here? Would you be able to share a sample?

[andrei_delta]

Hello,

Just an observation when you are using set analysis to calculate values, be sure to take the nulls out of that field (the one with the values) otherwise it will mess the result...

Regards,

Andrei

[sangeess21]

No. The users would like to see this as a pivot table so they can keep adding dimensions when they need.

[sangeess21]

The ID is the ID of the customer. If the ID is 1,2 or 3 then they are Direct Customers else Indirect Customers. I would love to share a sample, but it has sensitive information in it.

[sunny_talwar]

See if this addresses your confidentiality concerns

Preparing examples for Upload - Reduction and Data Scrambling

[sangeess21]

I created a sample of the example I posted above. Hope this helps. Thanks!

[sunny_talwar]

Try this

Sum({<ID = {'1','2'}, Year = {$(=Year(Today(1)))}, Customer = {"=Sum({<ID = {'1','2'}, Year -= {$(=Year(Today(1)))}>}Value) = 0"}>}Value)

[sangeess21]

Thanks Sunny. This works for the above scenario, but not mine. I've attached the sample where the above set analysis doesn't work. I would like only Customer B, D, E and F to pop up as new customers as they do not have any value in the past.