Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Month | Worker | Group1 | Group2 | Group3 |
---|---|---|---|---|
3 | jhon | 2 | 1 | 1 |
3 | Peter | 1 | 2 | 0 |
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
Create the groups in the script instead:
LOAD
store,
worker,
invoice_no,
sa_total_without_tax,
month,
if(sa_total_without_tax<200,'Group1',
if(sa_total_without_tax<300,'Group2','Group3')) as Group
FROM ...somesource...;