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!
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)
Would you like to do Cross Table?
What is ID field here? Would you be able to share a sample?
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
No. The users would like to see this as a pivot table so they can keep adding dimensions when they need.
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.
See if this addresses your confidentiality concerns
Preparing examples for Upload - Reduction and Data Scrambling
I created a sample of the example I posted above. Hope this helps. Thanks!
Try this
Sum({<ID = {'1','2'}, Year = {$(=Year(Today(1)))}, Customer = {"=Sum({<ID = {'1','2'}, Year -= {$(=Year(Today(1)))}>}Value) = 0"}>}Value)
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.