Qlik Community

Ask a Question

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 
Search instead for 
Did you mean: 
JustinM
Contributor III
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):

CovPeriodRiskidTxValue
112500
122100
127-100
132200
137-50
212500
232200
237-200

 

Expected Answer

CovPeriodDistinct Count
12      (Effectively id 1 & 3) (ID 2 sums to zero)
21       (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
saran7de
Master
Master

Try this,

tab1:
LOAD * INLINE [
    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;
saran7de
Master
Master

Output:

commQV03.PNG

JustinM
Contributor III
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