topic Re: Distinct Column Totals for a Straight Table? in QlikView

Distinct Column Totals for a Straight Table?

jrdunson — Thu, 29 May 2014 15:33:35 GMT

Hey, I'm just wanting to show straight tables, yet for distinct values, per user selections:

ID	Key	Amount
0000093529	2092404	25
0000093529	2192572	25
0000758673	2164158	20
0000549740	2192638	0
0000549740	2193199	50
0000740998	2164156	25
0900300113	2107702	50
???	7	195
Unique ID Count?	Key count	Total Amount

How do I calculate a 'Unique ID Count' ? [i.e. should be 5] ...???

I've Google'd, tried to use Aggr(), Count DISTINCT, but haven't yet found a solution... Should this be simple?

Jarrell

Re: Distinct Column Totals for a Straight Table?

manideep78 — Thu, 29 May 2014 16:07:29 GMT

Hi,

Where do you want to use this ID Count. Is it in a text object or straight table.

If you want to show in a text object Count(Distinct ID) gives 5.

But if you want to show in a straight table you should remove Key. Since Key is unique and same ID is have 2 unique keys. you should see Id twice. if you remove Key then you can see sUnique Id in a table.

Re: Distinct Column Totals for a Straight Table?

maxgro — Thu, 29 May 2014 16:37:11 GMT

create a chart (straight table)

dimension Key

expression see below lables

Re: Distinct Column Totals for a Straight Table?

jrdunson — Thu, 29 May 2014 17:34:54 GMT

I tried this... and didn't work... my column results in zeros...and there is nothing in the total field.

My column is an expression: Count (Distinct [ID])

Here are my results (PS. my table has 943,000 rows in it...so, I didn't highlight the exact same rows as I did above, but here is another few rows as another example):

Re: Distinct Column Totals for a Straight Table?

manideep78 — Thu, 29 May 2014 17:40:44 GMT

make sure in the expressions tab, total mode is set to expression total.

Re: Distinct Column Totals for a Straight Table?

jrdunson — Thu, 29 May 2014 17:49:57 GMT

It is... already...

Re: Distinct Column Totals for a Straight Table?

jrdunson — Thu, 29 May 2014 17:51:10 GMT

Sorry, I can't remove the 'Key' field

Re: Distinct Column Totals for a Straight Table?

manideep78 — Thu, 29 May 2014 18:08:15 GMT

Can you please upload your app with some sample data.

Re: Distinct Column Totals for a Straight Table?

jrdunson — Thu, 29 May 2014 19:06:20 GMT

Hey everyone,

I figure this out...

- I replaced this: count(DISTINCT [ID])

- With this: num(count(DISTINCT [ID]), '#,##0')

.. adding the num() make it work.