Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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)
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!
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)
Thank you, Sunny It working now. Can you please explain what you are doing here? Why is ID added in the Customer set analysis?
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
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!
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)
Ok. Thank you so much