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
]
