topic Re: No Sales customers in QlikView

No Sales customers

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I need to write query in Qlik Sense to get the count of the customers in Yellow because they are there in table 1 and they are not in table 2

Table 1
Customer Number	AR Balances
1244	1235
1234	100
1111	1200
2222	4555
Table 2
Customer Number	Sales
1111	2000
2222	1000
3333	400

Re: No Sales customers

Quy_Nguyen — Thu, 28 Jun 2018 19:47:20 GMT

Try this:

Count({<[Customer Number] = {"=Sum(Sales)=0"}>}[Customer Number])

Re: No Sales customers

Anonymous — Fri, 29 Jun 2018 04:04:08 GMT

Hi Quy Nguyen,

Thanks for the quick response , this helped to some extend , let me put the complete problem, I just want to count if AR Balance is <0 and there is no sales at all in sales tables , hence in the below example

The count should be 2 and the customers are (1244 and 2233) because these two guys either not present in Sales table or there is no entry in the sales table, plus they have negative AR balance.

Note : the customer 1244 having 2 line items with negative value and there is no entry in the sales table that means the count should be only 1 even though there are 2 entries in the customer table. The overall output should be 2.

Table 1			Sales Table
Doc number	TB 1. Customer Number	AR Balances	ST.Customer umber	Total Sales
1	1244	-1235	2233	0
2	1244	-100	111	5000
3	1244	1200
4	1244	4555
5	1244	400
6	1244	500
7	1244	600
1	2233	-100
2	2233	-300
3	2233	-400
34	2233	100
45	2233	200
1	111	123
2	111	234
3	111	456
4	111	678
5	111	890
6	111	890

Re: No Sales customers

Quy_Nguyen — Fri, 29 Jun 2018 15:28:06 GMT

Try it, i just add Distinct to remove duplicate value

Count({<[Customer Number] = {"=Sum(Sales)=0"}>} Distinct [Customer Number])