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

    aggr() function to sum up transaction data

    raz herman

      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