topic Re: Distinct Count in QlikView

Distinct Count

Sun, 21 May 2017 05:38:48 GMT

Hi all

i write an expression for counting distinct customers who bought from us with two condition:

1) at least they bought 1 product from us

2) our sales to them minus sales returns be greater than zero

so i write this:

sum(aggr(DISTINCT(if((sum(SalesQTY*Price)>0) and ((sum(SalesQTY*Price)-sum(SalesReturnsQTY*Price))>0,1,0)),Customer's ID))

i used Date (YY-MM) as dimension but it shows me "No data to display"!

is my formula right?

tnx in advance

Re: Distinct Count

Anil_Babu_Samineni — Sun, 21 May 2017 05:45:14 GMT

Can you please share application if possible that demonstrates the issue?

Re: Distinct Count

Sergey_Shuklin — Sun, 21 May 2017 05:47:15 GMT

Hello, Ahmad!

May be clue in the brackets?

sum(aggr(DISTINCT(if((sum(SalesQTY*Price)>0) and ((sum(SalesQTY*Price)-sum(SalesReturnsQTY*Price))>0,1,0)),[Customer's ID]))

Re: Distinct Count

Sun, 21 May 2017 05:53:15 GMT

Qlikview 11

Re: Distinct Count

Anil_Babu_Samineni — Sun, 21 May 2017 05:55:02 GMT

I didn't ask you provide the s/w version any more? I am asking about application whether how work around is going on and where we need to resolve the issue.

Re: Distinct Count

Sun, 21 May 2017 05:56:00 GMT

Hi Sergey

No, i tried, it does not work

Re: Distinct Count

Sergey_Shuklin — Sun, 21 May 2017 06:03:33 GMT

So, try this condition:

sum(aggr(distinct sum(if(sum(aggr(SalesQTY*Price,Customer))>0 and (sum(aggr(SalesQTY*Price,Customer))-sum(aggr(SalesReturnsQTY*Price,Customer)))>0,1,0)), Customer))

Re: Distinct Count

Sun, 21 May 2017 06:03:37 GMT

i used a bar chart to shows data

recently our It team change the Qlikview version to 12 on server but right now i'm using version 11

the odd thing is when i open my chart on the server it works perfectly but in my PC or any other device it does not work!!

before they upgrade to version 12, it was working great. is it possible that upgrading version cause this?

Re: Distinct Count

Sergey_Shuklin — Sun, 21 May 2017 06:09:41 GMT

Or make it simle:

Count(distinct {<Customer_bought={">0"}>}Customer)-Count(distinct {<Customer_returned={">0"}>}Customer)

Re: Distinct Count

Sun, 21 May 2017 06:14:56 GMT

No luck with this either

Re: Distinct Count

Sun, 21 May 2017 06:16:05 GMT

in my expression there is an "And", i think you missed it in your formula

Re: Distinct Count

Anil_Babu_Samineni — Sun, 21 May 2017 06:18:50 GMT

Try this first and look how this went?

If(Sum(SalesQTY*Price)>0 and (Sum(SalesQTY*Price)-Sum(SalesReturnsQTY*Price))>0, 1,0)

Then Try with out Summation

Aggr(If(Sum(SalesQTY*Price)>0 and (Sum(SalesQTY*Price)-Sum(SalesReturnsQTY*Price))>0, 1,0), [Customer's ID])

And then look and share snapshot for it

Re: Distinct Count

tamilarasu — Sun, 21 May 2017 06:30:33 GMT

Hi Ahmad,

Sum(Aggr(If((Sum(SalesQTY*Price)>0) And ((Sum(SalesQTY*Price)-Sum(SalesReturnsQTY*Price))>0),1,0),[Customer's ID]))

Re: Distinct Count

Sun, 21 May 2017 06:45:27 GMT

First one worked but the moment i used aggr function it shows me "No data to display"

Re: Distinct Count

Anil_Babu_Samineni — Sun, 21 May 2017 06:46:40 GMT

Switch to Pivot whether you are in Straight?

Re: Distinct Count

Sun, 21 May 2017 06:47:33 GMT

No it does not work

Re: Distinct Count

tamilarasu — Sun, 21 May 2017 06:50:17 GMT

Can you upload a sample file?

Re: Distinct Count

Sun, 21 May 2017 06:51:50 GMT

No i'm sorry ,all the data are in server i dont have access to them

Re: Distinct Count

tamilarasu — Sun, 21 May 2017 06:59:43 GMT

Create a straight table and add Customer as dimension and add below expression. If you are getting correct result then the aggr should work.

If((Sum(SalesQTY*Price)>0) And ((Sum(SalesQTY*Price)-Sum(SalesReturnsQTY*Price))>0),1,0)

Re: Distinct Count

Kushal_Chawda — Sun, 21 May 2017 11:38:43 GMT

try this

=count( DISTINCT {<CustomerID ={"=sum(SalesQTY*Price)>0 and sum(SalesQTY*Price)-sum(SalesReturnsQTY*Price)>0"}>}CustomerID)