Count Distinct Multiple sheets

[malradi88]

Dear Qlik Community,

I hope this finds you well. I am currently attempting to develop multiple KPIs to track:

# Loan recipients (income growth > = 10%) Grade I Loans

# Loan recipients (income growth > = 15%) Grade II Loans

# Loan recipients (income growth > = 20%) Grade III Loans

The data-set that I am working with consists of 4 excel files tracking progress throughout a year. Each sheet resembles the following:

Name	Loan Grade	Loan Amount	Pre Loan Monthly Income	Current Monthly Income
tom	1	$5000	$2500	$3000
Emily	1	$5000	$2000	$2700
bob	3	$7000	$3000	$3200
mark	4	$9000	$3200	$4500
Nancy	4	$9000	$4000	$4400

I am having trouble calculating the above-mentioned indicators accurately.

1) When I use this formula:

count({< [Name]={'=sum([IncomeIncease])>= 0.1'},[Loan Grade]= {'1'}>}distinct [Name])

The results are right (always a bit off though from the manual count I do in excel) when I use a filter pane to select one of the excel files that informs the app. But when I remove the filter to take into acccount all the files... the number goes way off (why would it go so off? the number actually becomes less than if I selected one file from the filter pane).

2) When I use this formula:

sum( aggr(count({$< [Name]={'=sum([IncomeIncease])>= 0.1'},[Loan Grade]= {'1'}>} distinct [Name]),[Loan Grade],filename))

The result stays the same as the above when using the filter pane to select one of the excel files that informs the app and 'less wrong...just a bit off rather than hugely off' when de-selecting the file-name filter.

Moreover, for both expressions, when I click on 1 in the 'Loan grade'  filter pane the results change. Why would it change further if that very same loan grade is included in the set expression?

3) Query:

I think I am going a bit crazy manually counting results and comparing them to qlik results constantly going between sum(aggr(count expressions and standard ones. At this point I am just trying different combos without really understanding them very well...hoping for the best. 

Keeping in mind that many names are repeated in each of the 4 excel files what is the best expression to use to accurately count the number of recipients who have incerased their income by x% in loan grade x? Why do the results vary depending on whether I use the aggr(sum or standard formulas?

Any advice would be most appreciated.

Best,

Mohammed

[ogautier62]

Hi,

maybe could you post a short example of each file ?

have you negative incom growth in some file ?

if so, count will be less than for one file with positive growth

for second question :

is it possible same name with different loan grade in files ?

regards