Total Basket Sales

Fri, 15 Nov 2013 22:39:41 GMT

Hi,

I am trying to create a chart that shows total basket size in dollars for any item. We want to answer questions like how much money does a cusrtomer spend when they buy bananas ... or laundry detergent.

I am able to identify the transactions containing the item using the following:

Concat ({1<Transaction = p(Transaction)>} Transaction, ', ')

Creating a chart with the item dimension and this formula returns the following ... which is correct:

Item	HH count	TX count	concat TX
Bananas	2	2	1, 2
Bread	1	1	1
Chicken	1	1	1
Crackers	1	2	1, 3
Milk	1	2	1, 3
Pasta	2	2	1, 2
Pasta Sauce	2	2	1, 2
Soup	1	2	1, 3

I then want to find the total sales of the transactions that had those items. I do this using the following:

Sum (TOTAL{1<Transaction={$(=Concat({1<Transaction = p(Transaction), Customer=p(Customer)>} Transaction, ','))}>} [Sales Dollars])

However, this formula brings back the total sales of all transactions ... not just the sales for the transactions the item participated in.

Is there something wrong with the syntax of the concat clause?

Sample information:

LOAD * INLINE [

Transaction, Customer, Dept, Item, Sales Dollars

1, 1, Grocery, Soup, 1

1, 1, Grocery, Crackers, 3

1, 1, Dairy, Milk, 4.5

1, 1, Grocery, Bread, 2

1, 1, Produce, Bananas, 1.5

1, 1, Meats, Chicken, 5

1, 1, Grocery, Pasta, 3

1, 1, Grocery, Pasta Sauce, 2

2, 2, Produce, Bananas, 2

2, 2, Grocery, Pasta, 3

2, 2, Grocery, Pasta Sauce, 4

3, 1, Grocery, Soup, 1

3, 1, Grocery, Crackers, 3

3, 1, Dairy, Milk, 4.5

]

Re: Total Basket Sales

israrkhan — Sat, 16 Nov 2013 13:55:47 GMT

Hi Try Below:

Sum (TOTAL{1<Transaction={$(=Concat({<Transaction = p(Transaction), Customer=p(Customer)>} Transaction, ','))}>} [Sales Dollars])

Re: Total Basket Sales

MK_QSL — Sat, 16 Nov 2013 18:02:58 GMT

Try This....

SUM({<Transaction={$(=Concat({<Transaction = p(Transaction), Customer=p(Customer)>} Transaction, ','))}>} [Sales Dollars])

topic Total Basket Sales in QlikView

Total Basket Sales

Re: Total Basket Sales

Re: Total Basket Sales