Distinct Count

[#1]

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

[#2]

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

[Anil_Babu_Samineni]

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

[tamilarasu]

Hi Ahmad,

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

[#5]

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

[Anil_Babu_Samineni]

Switch to Pivot whether you are in Straight?

[#7]

No it does not work

[tamilarasu]

Can you upload a sample file?

[#9]

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

[tamilarasu]

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)

[Kushal_Chawda]

try this

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