giving credit for a new customer only to the salesperson who made the first sale

[Dparchom]

Hello,

I am analysing new customers brought in to the company for a specific product range.

The goal is to determine which sales person made the 1st sale within the criteria and give him/her 1 point per new customer. 

Dimensions: Product type, Customer, Salesperson

Range: Current Month/Year vs Previous Month/Year

Lets say we have 3 different product types.

If the customer did not buy anything last year within these product types, but bought it this year, then give +1 to the sales person who made the 1st sale this year. 

My current issue is correctly assigning the point to the salesperson with the first sale.

Code example below for MTD:

Aggr(Sum({$<Year={"$(=Max(Year))"}
,Month={"$(=Only(Month))"},[Product_Type]={'Heater'}
,ProductDriver={'=Sum({$<Year={"$(=Max(Year))"},Month={"$(=Only(Month))"}>}
Aggr(Sum({$<Year={"$(=Max(Year))"},Month={"$(=Only(Month))"}>} InvoiceValue_),ProductDriver))>0'}
,Customer_KEY = E({<Year={'$(=Max(Year))-1'},Month={"$(=Only(Month))"}
,[Product_Type]={'Heater'}
,ProductDriver={'=Sum({$<Year={"$(=Max(Year))-1"},Month={"$(=Only(Month))"}>}
Aggr(Sum({$<Year={"$(=Max(Year))-1"},Month={"$(=Only(Month))"}>} InvoiceValue_),ProductDriver))>0'}>} Customer_KEY)
>}Customer_Count),Customer_KEY,[Doc Created By])

Attached current result vs expected.

[Ksrinivasan]

hi,

pl, provide sales data

ksrinivasan

[Dparchom]

Hi,

Thanks for showing interest.

Attached a data example. The main goal is to get the Total Unique column, breakdown by product is secondary.

Assume these are all new, but there needs to be a check against last years data in the set analysis and only sales > 0 need to taken in to account. I changed some of the column headers manually.

Below is the latest code example I've got, it assigns the count correctly when the Following dimensions are selected: Year, Month, Customer No, but I need it to work when only Year, Month is selected.

Hope it makes sense, thanks for the help.

Sum({$<Year={"$(=Max(Year))"}
,Month={"$(=Only(Month))"}
,[Fridge Direct]={'Prod1'}
,[Doc Created By]={'=Sum({$<Year={"$(=Max(Year))"},Date={"$(=Min(Date))"},Month={"$(=Only(Month))"}>}
Aggr(Sum({$<Year={"$(=Max(Year))"},Month={"$(=Only(Month))"},Date={"$(=Min(Date))"}>} InvoiceValue_),Customer_KEY))>0'}
,FDCustomerDriver={'=Sum({$<Year={"$(=Max(Year))"},[Fridge Direct]={"Prod1"},Month={"$(=Only(Month))"}>}
Aggr(Sum({$<Year={"$(=Max(Year))"},Month={"$(=Only(Month))"}>} InvoiceValue_),FDCustomerDriver,[Doc Created By]))>0'}
,Customer_KEY = E({<Year={'$(=Max(Year))-1'}
,Month={"$(=Only(Month))"}
,[Fridge Direct]={'Prod1'}
,FDCustomerDriver={'=Sum({$<Year={"$(=Max(Year))-1"},Month={"$(=Only(Month))"}>}
Aggr(Sum({$<Year={"$(=Max(Year))-1"},Month={"$(=Only(Month))"}>} InvoiceValue_),FDCustomerDriver,[Doc Created By]))>0'}>} Customer_KEY)
>}Customer_Count)

[Ksrinivasan]

hi,

try this script:

Prod_Cust:
LOAD
Company,
"Customer No",
Customer_KEY,
"Doc Created By",
"Year",
"Month",
"Invoice Date",
"Fridge Direct",
FDCustomerDriver,
InvoiceValue_,
Customer_Count,
if(AutoNumber(RowNo(),"Customer No"&"Doc Created By")='1','1','0') as Rank1
FROM [lib://REPORT EXTRACTION SSSS1.xlsx]
(ooxml, embedded labels, table is custoer_product);
//where Rank ='1';
NoConcatenate
Final:
Load *

Resident Prod_Cust
where match(Rank1,'1');
Drop table Prod_Cust;

Table:

DIM: "Doc Created By"

Mea:

(Sum({$<"Fridge Direct" = {"Prod1"}>}Rank1))

note: your expected data was wrong in your Excel.

ksrinivasan.