aggr() function to sum up transaction data

raz-datamind — Sun, 10 Mar 2013 08:26:33 GMT

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)

but it's not adding up:

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$

instead of :

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

topic aggr() function to sum up transaction data in QlikView