Getting a total based on the possible values from field selection

[#1]

Hi there,

My data model has basically 4 things, a customer, a purchase date, product, and sale amount.

My table is like this:

Cust	Date	Product	Amount
John	1/5/2016	Apple	$2
John	1/6/2016	Banana	$3
John	1/6/2016	Banana	$2
John	1/7/2016	Orange	$5
James	1/6/2016	Apple	$1
James	1/6/2016	Banana	$3
James	1/7/2016	Banana	$3
Jenny	1/5/2016	Banana	$3
Jenny	1/6/2016	Apple	$6
Jenny	1/7/2016	Orange	$2

I want to be able to get the total of whoever bought Apples but only on the date they bought the Apple.

If I select Apple in the field selection, I want to be able to get John's total on 1/5/2016, James total on 1/6/2016, and Jenny's total on 1/6/2016, coming out to be $12.

Is there a way to do this?

Thank you.

[#2]

Thank you for your response.

I believe I might have been a little confusing. The totals that I want for the output is the sum of all the products of the specific date according to when the apple was purchased.

Since John purchased the apple on 1/5/2016, I want to know his total on that day which is $2. James' total would be $4 since he purchased the apple on 1/6/2016 and Jenny's would be $6. Grand total of the apple selection would be $12.

I was wondering if there was a way to do this. Thanks again

[damirm86]

Something like this maybe?

[sunny_talwar]

For single purchase, this formula seems to be working:

=Sum({<Product>}Aggr(If(Only({<Product>}Date) = Only(TOTAL <Cust> Date), Only({<Product>}Amount)), Cust, Date, Product))

Works for Apple, but doesn't work for Banana. What would be the output for selection in Banana?

[#5 · Accepted solution]

How about like this?

[sunny_talwar]

Looks good

[effinty2112]

Hi Ming,

What about :

Date	Cust	Sum(Aggr(If(index(aggr(Concat(Product,'|'),Cust,Date),'Apple'),Sum(Amount)),Cust,Date))
		12
1/5/2016	John	2
1/6/2016	James	4
1/6/2016	Jenny	6

[#8]

Thanks, Sunny!  Learned only() from you.

[#9]

It shows the total for the product. I still need the date total based on the product selection if you can help. Thanks

[#10]

Wow! This is exactly what I'm looking for. Thank you all your help. Much appreciated.