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
