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!

[sunny_talwar]

What is the expected output here? This?

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

[sangeess21]

The above expression returns only Direct. I would like to return new customers in Indirect too. The result should be like

Direct/Indirect Customer	Service Class	Customer	SP Value
Direct	Service A	Customer B	$638.19
Direct	Service A	Customer E	$29,804.20
Direct	Service B	Customer B	$2,400.18
Direct	Service B	Customer D	$605.00
Direct	Service B	Customer E	$5439.27
Indirect	Service A	customer F	$117.00
Indirect	Service C	Customer F	$85.14

Thanks!

[sunny_talwar · Accepted solution]

Indirect doesn't have ID 700000, 700001 or 700002... that is why they did not show up... may be try this

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

[sangeess21]

Thank you, Sunny It working now. Can you please explain what you are doing here? Why is ID added in the Customer set analysis?

[sunny_talwar]

So, the expression is showing the sum of sales for all customers who have no sales in last years within the ID 700000, 700001, and 700002 and show there sales in this year.

Why is ID added in the Customer set analysis?

Have no idea why you had them in the first place? I just tried to use your expression and expand on it

[sangeess21]

Thank you, Sunny. I was trying to filter by ID for Direct and Indirect customers in my expression. I have one more question. Though we are filtering customers who has no sales in last years in the expression, why does Customer G show up in the result when we select 'Service A' Service Class?

Thanks!

[sunny_talwar]

It sort of depends on what your final expectations are with regards to this expression, but you can play around with it to fix it... for instance the issue that you pointed out can be resolved by making this change

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

[sangeess21]

Ok. Thank you so much