Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
G'day,
I need your help in a small problem i'm stuck with.
I have a sales table that show the invoice_no, worker_no, product_id, total_amount
100, 555, 99889 , 70
100,555,99888,100
100,666,99999,50
101,555,77887,650
102,555,77777,250
103,666,78956,199
I want the create a table that shows the amount of invoices a worker have when the sales (of the invoice) is between 0 to 200, and the number of invoices between 201 to 500 and above 501.
the table should be like that
worker_no, #of invoices between 0 to 200, #of invoices between 201 to 500, #of invoices above 501
555,1,1,1
666,2,0,0
When I am adding the invoice_no to the table it works fine but when I take it out I am it sum all the invoices and not per invoice so I am getting:
555,0,0,3
666,0,2,0
I am using the following code:
if(sum(total <invoice_no,worker_no> total_amount) > 0 and sum(total <invoice_no,worker_no> total_amount) < 200 , (COUNT (DISTINCT total <invoice_no, worker_no> invoice_no)),0)
Thanks in advance
.
Hi,
the key is =count({<total_amount = {"<=501 >= 200"} >} invoice_no)
Please find attached my solution. Hope this helps.
See attached qvw.
Hi,
the key is =count({<total_amount = {"<=501 >= 200"} >} invoice_no)
Please find attached my solution. Hope this helps.
You can create something like this and you can only change the values of the limits for the value you want to use and create a count for the current selection for you to indicate the number of invoices per worker
Hi Yaniv,
Please see attached. I think I have managed to visualize the table you are asking for.
First, I have classified the values in "total_amount" by using IntervalMatch. You see that under the Description column.
Second, I have used Set Analysis so that I can specify for which Description value I want the expression to execute on.
Thank you all for your help.
SALTO solution was most suitable for my needs.