Aggr function - total sales from the purchase by each customer of a certain item

[txepulin]

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

[rubenmarin]

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))

[txepulin]

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.

[rubenmarin · Accepted solution]

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.

[txepulin]

It is exactly what I was looking for, it works perfectly. Thanks a lot.