Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Removal of double counting

We have downloaded government contract data into Excel and uploaded to Qlik Sense. It shows contract information. Each row shows information to an update to an existing contract. For example, 5 consecutive rows can show 6 annual updates for 1 contract (see highlighted rows in attached)

Our goal is to count the “Contract Office Name” only once per specific contract “Contract ID”. We want to have a pie graph representing the percentage of each Contracting Office Name but can't figure out how to use the functions to not double count with respect to the contract updates.

Thank you!Screenshot (14).png

8 Replies
kidbank3
Contributor III

Re: Removal of double counting

have you tried making the "Contract Office Name" your dimension and count(distinct "contract ID") as your expression?

Not applicable

Re: Removal of double counting

I tried inputting that and it said "The chart is not displayed because it contains only negative or zero values"

vinieme12
Esteemed Contributor II

Re: Removal of double counting

An efficient way would be to modify your load script and add a table to hold unique ContractID's

YourDataTable

LOAD *

FROM Xxxxxxxxx;

LOAD

     DISTINCT ContractID,

     1 as ID_Count

RESIDENT YourDataTable;

Now your expression will be = sum(ID_Count)   <<Sum works faster than Count

Cheers

V

kidbank3
Contributor III

Re: Removal of double counting

Can you provide a screenshot? 

Not applicable

Re: Removal of double counting

It was an error with parenthesis. I wrote it as count(distinct [Contracting Agency ID])

kidbank3
Contributor III

Re: Removal of double counting

Sarah,

count(distinct [Contracting Agency ID]) is syntactically correct.  Is it working now?

Not applicable

Re: Removal of double counting

I think so. is there a way to have it display more fields instead of "other"?Screenshot (15).png

kidbank3
Contributor III

Re: Removal of double counting

I think you have a dimension limitation set to 10.

Check the dimension limit under the dimension tab.  You'll want to set the Limitation drop down to No Limitation

2016-07-05 13_06_44-Qlik Sense Desktop.jpg

Community Browser