# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for
Did you mean:
Contributor III

## Distinct count based on result of sum calculations

Hi

I am trying to calculate a distinct count based on the result of a sum calculation on various items (only distinct count this ids where the sum of all tx in a CovPeriod do not equal 0):

 CovPeriod Riskid Tx Value 1 1 2 500 1 2 2 100 1 2 7 -100 1 3 2 200 1 3 7 -50 2 1 2 500 2 3 2 200 2 3 7 -200

 CovPeriod Distinct Count 1 2      (Effectively id 1 & 3) (ID 2 sums to zero) 2 1       (Effectivly Id 1 & 3) 33

I tried:  Count({<Sum(TOTAL <riskid,CovPer>Value)<>{'0'}>}Distinct [riskid]), but it threw out an expression error?

Any advice on where I'm going wrong or overcomplicating things would be much appreciated

3 Replies
Master

Try this,

``````tab1:
CovPeriod, Riskid, Tx, Value
1, 1, 2, 500
1, 2, 2, 100
1, 2, 7, -100
1, 3, 2, 200
1, 3, 7, -50
2, 1, 2, 500
2, 3, 2, 200
2, 3, 7, -200
];

Left Join(tab1)
LOAD CovPeriod, Riskid, Sum(Value) As CR_Value
Resident tab1
Group By CovPeriod, Riskid;``````
Master

Output:

Contributor III
Author

Thanks Saran

Please bear with me as I'm new at this. Appreciate your patience

1) Do I just enter the above solution as is into the expression or does it need to go into load script?

2)How do I capture the data part (1,1,2,500) etc as my data has 4m+ lines

Thanks