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]

Hi,

"Count(Distinct Document) returns 4" this is true because table has 4 unique Document ID.

"if ( isvoided='no', 1, -1) as salescount doesn't help in counting neither", field "Is voided" is "Yes" only at the document A, and the same document is above the "No".

Try this table

Document  |  Sales  |  Is voided |   Time

A               |  10      |   no          |  Time1

B               |  11      |   no          |  Time2

B               |  12      |   no          |  Time3

A               |  -10     |  no          |  Time4

C               |  11      |  Yes        |  Time5

D               |   -11    |   no          |  Time6

"Count(Distinct Document)" with "Is voided = No" will return 3.

[jcoggon]

Hi Calvin,

You could also use:

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

This will return a result of 3 when counting distinct Documents.

Just remember that the selection of "no" in the middle of the equation is case sensitive, so if you have a mixture of Upper and Lower case in your data, it would be best to replace your "Is voided" data script with UPPER([Is voided]) as [Is voided]

This way you will get a consistent "YES" and "NO" in your data.

[#4]

Thank Jason!

[#5]

Thanks for your trying to help!

[#6]

to confirm my concept, is that only B, C, D will be counted if use the formula you provided?

Thx

[preminqlik]

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

[swuehl · Accepted solution]

I do get a count of four documents with above solution, because all four documents are related to Is voided "no" value.

But try maybe

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

[#9]

I am sorry Jason, the solution seems not working ...

[jagan]

Hi Calvin,

You can exclude the documents which has Is Voided= Yes by using the E() in qlikview set analysis expression like this here E() eliminates A and returns B, C and D.  So you will get count 3.

=Count({<Document = E( {<[Is voided] = {"Yes"}>}) >} Distinct Document)

Hope this helps you.

Regards,

Jagan.