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
