topic Re: Selection and Without Selection, different count in App Development

Selection and Without Selection, different count

Subhasmita — Thu, 07 Jul 2022 12:56:46 GMT

Hi Experts!

I am facing a peculiar issue in my Qlik Sense app. Due to security concern, can't attach sample file, however will try to explain as much as possible.

I am trying to find out distinct count of customer id at contract level for sales having non zero values. I am using below expression.

Count(distinct
{<[Customer ID]={"=Sum(Total<[Contract ID], [Customer ID]> [Net Sales])>0"}>}
total <[Contract ID]> [Customer ID])

however this expression works well when contract ID is selected but when there is no contract id selection, count shows more which is not correct.

I have table similar to this.

Contract	Cust ID	sales
123	111	0
123	222	60
123	333	100
234	444	20
234	555	80

any leads will surely of great help, thanks!

Re: Selection and Without Selection, different count

edwin — Thu, 07 Jul 2022 15:27:15 GMT

try

i added a third contract to test sum=0:

Re: Selection and Without Selection, different count

Subhasmita — Thu, 07 Jul 2022 16:19:39 GMT

Thanks for checking on this Edwin!

I would need the count of customers for the contract which has positive sales. so the example you shared should give me ideally as below.

Contract	Cust ID	Prod sales	Count of Cust
123	333	100	2
123	222	60	2
123	111	0	2
234	555	80	2
234	444	20	2
456	500	50	1
456	200	-25	1
456	201	-25	1

this expression is giving me correct count when contract is selected but not when contract is not selected and we see contracts on the table.

Count(distinct
{<[Customer ID]={"=Sum(Total<[Contract ID], [Customer ID]> [Net Sales])>0"}>}
total <[Contract ID]> [Customer ID])

Re: Selection and Without Selection, different count

edwin — Fri, 08 Jul 2022 18:21:40 GMT

so your criteria is the slaes PER contract AND customer >0 and not sales per contract. is that rt?

Re: Selection and Without Selection, different count

edwin — Fri, 08 Jul 2022 18:45:05 GMT

the general solution is the same. using set analysis to find the sum that is > 0. however, this requires a single field so you need to fabricate that field:

note that i tweaked the data a little as well to test the last contract. cust id 200 will result in 0 total so that should not be included:

cust 111 is also not included

Re: Selection and Without Selection, different count

Subhasmita — Sat, 09 Jul 2022 16:50:49 GMT

thanks for the solution Edwin! it seems to work with the example data set.

Unfortunately, i have contract and cust ID coming from different tables, not sure how can i combine them.

thanks for looking the problem!

Re: Selection and Without Selection, different count

edwin — Sat, 09 Jul 2022 20:39:02 GMT

you dont need to combine tables. it looks like you dont have a proper DM yet which is a different problem from what youve posted. leave them separate and make sure you have a single common key between them. a contract must be with only one customer so cust ID would be your key