Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
egoziyan
Contributor II
Contributor II

count and sum

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
.

1 Solution

Accepted Solutions
salto
Specialist II
Specialist II

Hi,

the key is =count({<total_amount = {"<=501 >= 200"} >} invoice_no)

Please find attached my solution. Hope this helps.

View solution in original post

5 Replies
Gysbert_Wassenaar

See attached qvw.


talk is cheap, supply exceeds demand
salto
Specialist II
Specialist II

Hi,

the key is =count({<total_amount = {"<=501 >= 200"} >} invoice_no)

Please find attached my solution. Hope this helps.

Not applicable

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

Not applicable

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.

egoziyan
Contributor II
Contributor II
Author

Thank you all for your help.

SALTO solution was most suitable for my needs.