topic Re: Getting a total based on the possible values from field selection in QlikView

Getting a total based on the possible values from field selection

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Getting a total based on the possible values from field selection

Sat, 23 Jul 2016 20:25:24 GMT

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

Re: Getting a total based on the possible values from field selection

damirm86 — Sat, 23 Jul 2016 20:40:41 GMT

Something like this maybe?

Re: Getting a total based on the possible values from field selection

sunny_talwar — Sat, 23 Jul 2016 22:49:14 GMT

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?

Re: Getting a total based on the possible values from field selection

Sun, 24 Jul 2016 00:30:58 GMT

How about like this?

Re: Getting a total based on the possible values from field selection

sunny_talwar — Sun, 24 Jul 2016 15:43:01 GMT

Looks good

Re: Getting a total based on the possible values from field selection

effinty2112 — Sun, 24 Jul 2016 17:51:15 GMT

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

Re: Getting a total based on the possible values from field selection

Sun, 24 Jul 2016 18:05:46 GMT

Thanks, Sunny! Learned only() from you.

Re: Getting a total based on the possible values from field selection

Tue, 26 Jul 2016 14:51:50 GMT

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

Re: Getting a total based on the possible values from field selection

Tue, 26 Jul 2016 14:57:44 GMT

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