Help! A little bit tricky distinct counting!

[#1]

Dear All,

I have a sales ledger table, having structure like this:

Document  |  Sales  |  Is voided |   Time

A               |  10      |   no          |  Time1

B               |  11      |   no          |  Time2

B               |  12      |   no          |  Time3

A               |  -10     |   Yes        |  Time4

C               |  11      |   no          |  Time5

D               |   -11    |   no          | Time6

I want to find out the Sales no of Sales Chance

for Document A is voided if I select Time from  Time1 To Time6

Count(Distinct Document ) returns 4, but in this case i want 3,

And since Document is not distinct in this table (Take a look at B, one document have 2 row of sales amount)

Adding   if ( isvoided='no', 1, -1) as salescount     doesn't help in counting neither,

anyone get any ideas on it?

Many Thanks!^^

[#2]

=Count({<Document = e( {<[Is voided] = {"yes"}>}) >} Distinct Document)

my counting is targeting at the ( is void =  'no' ) documents which have no  ( is void = 'yes'  )document

so i guess yours is correct...

the concept is that   {<Document = e( {<[Is voided] = {"yes"}>}  excludes all documents with (isvoided = yes), it will also exclude the document having both yes and no record,

then the counting set is targeting clearly at the  un-void document,

swuehl, am i correct?

Thx!!!

[jagan]

Hi,

{<Document = e( {<[Is voided] = {"yes"}>}  excludes all documents with (isvoided = yes), it will also exclude the document having both yes and no records.

Regards,

Jagan.