Working on a project regarding banks and financial products, I came across a calculation that I've been unable to translate to QlikView.
The figure that needs to be shown needs to be calculated as follows: (number of users of a certain financial product at a chosen bank) / (number of users of that financial product among the customers of that bank)
Attached is a .QVW with dummy data and an example of the calculation.
Does anyone here have an idea how to accomplish this?
The difficult part of this calculation is that I need to know how many customers a bank has that use a specific product at any bank, do so at THAT certain bank
This is explained better with the example that's in the QVW:
take 'Bank 2', and take a look at 'Loans'
'Bank 2' has 4 customers, 3 of those customers are using the product 'Loan' at a bank, not necissarily bank 2, and 2 have a loan at bank 2.
In this example the calculation would be 2/3
So the correct answer is 66.7% It's not the "total customers that have a loan" that I need to divide by, but the "amount of clients of that bank that have a loan somewhere".
in the meantime, John Martin took a look at this problem too, and he provided a solution using a table not attached to the data, and a variable containing the selected bank in that field...
had a little time to look at your exam app. Let me add some notes (to explain what I have done):
- in the script I filtered the "isnt a client" and "not usage" --> these rows are not necessary; they even complicate coding the expressions (think about the if()-statements you do not need then). And within QV the user can always see at once elements that are implicit not or no more selected --> they have a grey background in listboxes.
for testing purposes I removed some show conditions
take a look at the table viewer. The sync-table is fine. Even some QT-Consultants are telling other things, esspecially to novices. For more details look around in this forum. John Witherspoon posted some good ideas and I agree with his oppinion of having a Sync-Table is fine when you think it is usefull and your messures are correct.
Last not least: Take a look at the expr in your chart. I think 40% loan is correct because I found 5 IDs with it. And two of them at bank2. So 2/5 = 40%.
The difficult part of this calculation is that I need to know how many customers a bank has that use a specific product at any bank, do so at THAT certain bank
This is explained better with the example that's in the QVW:
take 'Bank 2', and take a look at 'Loans'
'Bank 2' has 4 customers, 3 of those customers are using the product 'Loan' at a bank, not necissarily bank 2, and 2 have a loan at bank 2.
In this example the calculation would be 2/3
So the correct answer is 66.7% It's not the "total customers that have a loan" that I need to divide by, but the "amount of clients of that bank that have a loan somewhere".
in the meantime, John Martin took a look at this problem too, and he provided a solution using a table not attached to the data, and a variable containing the selected bank in that field...
