Removal of double counting

[#1]

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!

[mgranillo]

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

[#3]

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

[vinieme12]

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

[mgranillo]

Can you provide a screenshot? 

[#6]

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

[mgranillo]

Sarah,

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

[#8]

I think so. is there a way to have it display more fields instead of "other"?

[mgranillo]

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