Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Not applicable

Distinct Count

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

Tags (2)
19 Replies
Highlighted

Re: Distinct Count

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Sergey_Shuklin
Valued Contributor

Re: Distinct Count

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]))

Highlighted
Not applicable

Re: Distinct Count

Qlikview 11

Highlighted

Re: Distinct Count

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.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Not applicable

Re: Distinct Count

Hi Sergey

No, i tried, it does not work

Highlighted
Sergey_Shuklin
Valued Contributor

Re: Distinct Count

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))

Highlighted
Not applicable

Re: Distinct Count

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?

Highlighted
Sergey_Shuklin
Valued Contributor

Re: Distinct Count

Or make it simle:

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

Highlighted
Not applicable

Re: Distinct Count

No luck with this either