topic Re: Count Distinct Exclude Total Row in QlikView

Count Distinct Exclude Total Row

Tue, 29 Sep 2015 19:52:49 GMT

I have a chart with a total row. When I execute the =count(distinct(field name)) the total includes the row with the totals. Is there a way to eliminate the total row in the count? Thanks.

Re: Count Distinct Exclude Total Row

sunny_talwar — Tue, 29 Sep 2015 20:02:18 GMT

Not sure what you mean, but try this:

Sum(Aggr(Count(DISTINCT FieldName), YourChartDimensions))

Re: Count Distinct Exclude Total Row

Tue, 29 Sep 2015 20:08:16 GMT

Thanks, but that did not work. Here is an example. There are 8 invoices but the count = 9.

Re: Count Distinct Exclude Total Row

sunny_talwar — Tue, 29 Sep 2015 20:12:43 GMT

Is Customer Name and Inv Nbr the only two dimensions in this chart? and when you did this, it did not give you the correct total up-top -> Sum(Aggr(Count(DISTINCT FieldName), [Customer Name], [Inv Nbr]))?

Re: Count Distinct Exclude Total Row

Tue, 29 Sep 2015 20:18:59 GMT

There are 8 dimensions in the table. I used Sum(Aggr(Count(DISTINCT [Invoice Number]), [Invoice Number])) since I'm trying to determine the invoice count.

Re: Count Distinct Exclude Total Row

sunny_talwar — Tue, 29 Sep 2015 20:20:28 GMT

Try the above expression by adding all dimensions in it and see if it gives you the desired output.

Re: Count Distinct Exclude Total Row

Tue, 29 Sep 2015 20:34:56 GMT

It gave me a count of 20

Re: Count Distinct Exclude Total Row

Gabriel — Tue, 29 Sep 2015 21:19:15 GMT

Hi,

If you have Primary Key in your data model I suggest count the Primary key.

Like ID sort of

Re: Count Distinct Exclude Total Row

avinashelite — Wed, 30 Sep 2015 04:56:40 GMT

Share your sample app and data will see whats the issues is

Re: Count Distinct Exclude Total Row

PrashantSangle — Wed, 30 Sep 2015 05:20:58 GMT

Hi,

Are you selecting Supress when Value is Null option from dimension tab.

Regards

Re: Count Distinct Exclude Total Row

Anonymous — Wed, 30 Sep 2015 12:49:09 GMT

Yes, suppress when null is checked

Re: Count Distinct Exclude Total Row

Wed, 30 Sep 2015 16:49:47 GMT

When I created a sample app to share the count function worked as desired.

Re: Count Distinct Exclude Total Row

avinashelite — Thu, 01 Oct 2015 07:20:04 GMT

Cool , if you got the answer ..please mark the correct answer and close this thread so that will be helpful for others

Re: Count Distinct Exclude Total Row

Thu, 01 Oct 2015 12:06:54 GMT

Unfortunately the problem still exists in my original app.

Re: Count Distinct Exclude Total Row

PrashantSangle — Thu, 01 Oct 2015 13:58:32 GMT

Hi,

Because of it you are getting 1 extra count.

Either you can subtract 1 manually from your expression or handle null value dimension in your expression

Regards