1 Reply Latest reply: Sep 16, 2013 1:05 PM by Gysbert Wassenaar RSS

    Sum total issue

    Yaniv Egozi

      G'day,

      i need your advice with an issue I'm dealing with.

      I want to combine the number of invoices into group of sales (group 1 - number of invoices between 0 - 200 $, group 2 number of invoices between 201 - 300 , group 3  number of invoices abouve $300)

       

      My data is:

      store, worker, invoice_no, sa_total_without_tax, month

      11,jhon, 10055,170,3

      11,jhon,10056,180,3

      11,jhon,10057,280,3

      11,jhon,10058,400,3

      11,Peter,10059,180,3

      11,Peter,10060,200,3

      11,Peter,10061,250,3

       

      the result should be like

      MonthWorkerGroup1Group2Group3
      3jhon211
      3Peter120

       

      My problem is that I am getting all the invoices into group 3 when I am droping the invoice_no dimention.

      my expression is

      Group1 -

      (sum(total <INVOICE_NO, Worker> SA_TOTAL_WITHOUT_TAX) > 0 and sum(total <INVOICE_NO, Worker> SA_TOTAL_WITHOUT_TAX) < 200 , (COUNT (DISTINCT total <SA_INVOICE_NO, WorkerName> SA_INVOICE_NO)),0))

      Group2 -

      (sum(total <INVOICE_NO, Worker> SA_TOTAL_WITHOUT_TAX) > 200 and sum(total <INVOICE_NO, Worker> SA_TOTAL_WITHOUT_TAX) < 300 , (COUNT (DISTINCT total <SA_INVOICE_NO, WorkerName> SA_INVOICE_NO)),0))

      Group3 -

      (sum(total <INVOICE_NO, Worker> SA_TOTAL_WITHOUT_TAX) > 300 , (COUNT (DISTINCT total <SA_INVOICE_NO, WorkerName> SA_INVOICE_NO)),0))

       

       

       

      Thanks in advance

      Yaniv