topic Re: Help! A little bit tricky distinct counting! in QlikView

Help! A little bit tricky distinct counting!

Wed, 09 Oct 2013 03:36:40 GMT

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!^^

Re: Help! A little bit tricky distinct counting!

Wed, 09 Oct 2013 04:09:09 GMT

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.

Re: Help! A little bit tricky distinct counting!

jcoggon — Wed, 09 Oct 2013 05:13:39 GMT

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.

Re: Help! A little bit tricky distinct counting!

Thu, 10 Oct 2013 07:33:27 GMT

Thank Jason!

Re: Help! A little bit tricky distinct counting!

Thu, 10 Oct 2013 07:34:41 GMT

Thanks for your trying to help!

Re: Help! A little bit tricky distinct counting!

Thu, 10 Oct 2013 13:54:12 GMT

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

Thx

Re: Help! A little bit tricky distinct counting!

preminqlik — Thu, 10 Oct 2013 15:02:42 GMT

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

Re: Help! A little bit tricky distinct counting!

swuehl — Thu, 10 Oct 2013 15:11:20 GMT

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)

Re: Help! A little bit tricky distinct counting!

Fri, 11 Oct 2013 02:19:59 GMT

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

Re: Help! A little bit tricky distinct counting!

jagan — Fri, 11 Oct 2013 02:39:09 GMT

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.

Re: Help! A little bit tricky distinct counting!

Fri, 11 Oct 2013 02:55:01 GMT

=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!!!

Re: Help! A little bit tricky distinct counting!

jagan — Fri, 11 Oct 2013 03:19:55 GMT

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.