0 Replies Latest reply: Mar 10, 2013 4:26 AM by raz herman

# aggr() function to sum up transaction data

Hello,

I have a data of transactions:

category, supplier, brand, item, etc'.

each transaction may contain more then one row, category has several suppliers, which has several brand, which has several items.

also for each transaction row i have a field indicating the number of clients (1,0 or -1)

I want to create two tables with category, supplier & brand as dimensions,

and sum(amount) over quantities as expressions, in one table. and sum(clients) over quantities in the second

meaning my expressions are sum(amount) for all transaction that has 1 item, sum(amount) for all transaction that has 2 items, etc'.

and sum(clients) for all transaction that has 1 item, sum(clients) for all transaction that has 2 items, etc'.

i'm trying to use the expressions:

sum((if(aggr(sum(Sales_Qnty), transaction, category, supplier, brand)=1,amount)

sum((if(aggr(sum(Sales_Qnty), transaction, category, supplier, brand)=1,clients)

i get 1 client for:

make up, accessories, revlon

make up, accessories, D&G

but when i collapse brand i get

make up, accessories, 2 client.

when the correct answer is 1 client, as 1 client made the all transaction buying all those items.

on the amount table on the other hand i have a brand with two items, each costs 1.5\$.

i get a line:

sweets & food, sweets, must, 1.5\$

sweets & food, sweets, must, 3\$

and of course both table don't add up when i choose more than one transaction.

Anyone can direct me in the right way?

Do i use expreesions, or can i create calculated dimension to get the 1,2,3,... quantities as dimension?

Thanks,

Raz