Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
in my expression there is an "And", i think you missed it in your formula
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
Hi Ahmad,
Sum(Aggr(If((Sum(SalesQTY*Price)>0) And ((Sum(SalesQTY*Price)-Sum(SalesReturnsQTY*Price))>0),1,0),[Customer's ID]))
First one worked but the moment i used aggr function it shows me "No data to display"
Switch to Pivot whether you are in Straight?
No it does not work
Can you upload a sample file?
No i'm sorry ,all the data are in server i dont have access to them
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)
try this
=count( DISTINCT {<CustomerID ={"=sum(SalesQTY*Price)>0 and sum(SalesQTY*Price)-sum(SalesReturnsQTY*Price)>0"}>}CustomerID)