Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am trying to know the total sales generated from the purchase by each customer of a certain item, for this I use the formula:
sum(aggr(sum({<CustomerCode={"=sum({<ArticleCode={'1479490.91'}>} CustomerCounter)>0"},BillDate={">=$(=Date(min({<ArticleCode= {'1479490.91'}>}BillDate)))"},Group2Code=-{15}>} NetAmount),CustomerCode))
but I get the total sales from the first purchase of the item by any of the customers, not from each specific customer. How can I get it?
Any help is welcome.
Thank you all
Hi, if this is for a fixed analysis of that article, it will be better to flag the records on script. Doing a resident load of the data to identify the first purchase, and uing this value to flag the records after the first purchase, so the expression would be a simple: Sum({<isAfterFirstPurchase={1}>} NetAmount), and the additional set analysis to compare CY and LY.
The script could be something as:
// Set the date of the first purchase by each customer
Left Join (DataTable) //Name of the table with the data
LOAD
CustomerCode,
Min(BillDate) as FirstPurchase
Resident
DataTable
Where
ArticleCode='1479490.91'
and Group2Code<>15
Group By
CustomerCode
;
// Compare both dates to flag the records
NewData:
LOAD
ArticleCode,
BillDate,
Group2Code,
NetAmount,
CustomerCode,
If(BillDate>=FirstPurchase,1,0) as isAfterFirstPurchase
Resident
DataTable
;
DROP Table DataTable;
Or without the flag you can try with this expression:
sum(aggr(If(BillDate>=min(TOTAL <CustomerCode> {<ArticleCode={'1479490.91'}>}BillDate), sum({<Group2Code={"*"}-{15}>} NetAmount)),CustomerCode,BillDate))
But this would have more impact on performance on analysis time.
Hi, if set analysis sets the CustomerCode it will overwrite the CustomerCode used by the aggr, also the min BillDate would be calculated globally, not by each customer, maybe you can try with:
sum(aggr(FirstSortedValue({<ArticleCode={'1479490.91'},Group2Code=-{15}>} NetAmount,BillDate),CustomerCode))
Hello,
Thank you very much for your answer. I think I didn't know how to explain myself; with the expression you propose I obtain the purchases but only of this article, my objective is for each client to know the total purchases, of any article, from the purchase of one in particular and to perform the comparison with their purchases a year ago for rate the impact of purchasing this item.
Hi, if this is for a fixed analysis of that article, it will be better to flag the records on script. Doing a resident load of the data to identify the first purchase, and uing this value to flag the records after the first purchase, so the expression would be a simple: Sum({<isAfterFirstPurchase={1}>} NetAmount), and the additional set analysis to compare CY and LY.
The script could be something as:
// Set the date of the first purchase by each customer
Left Join (DataTable) //Name of the table with the data
LOAD
CustomerCode,
Min(BillDate) as FirstPurchase
Resident
DataTable
Where
ArticleCode='1479490.91'
and Group2Code<>15
Group By
CustomerCode
;
// Compare both dates to flag the records
NewData:
LOAD
ArticleCode,
BillDate,
Group2Code,
NetAmount,
CustomerCode,
If(BillDate>=FirstPurchase,1,0) as isAfterFirstPurchase
Resident
DataTable
;
DROP Table DataTable;
Or without the flag you can try with this expression:
sum(aggr(If(BillDate>=min(TOTAL <CustomerCode> {<ArticleCode={'1479490.91'}>}BillDate), sum({<Group2Code={"*"}-{15}>} NetAmount)),CustomerCode,BillDate))
But this would have more impact on performance on analysis time.
It is exactly what I was looking for, it works perfectly. Thanks a lot.