topic set analysis in QlikView

set analysis

Tue, 28 Dec 2010 15:38:43 GMT

L.S.,

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?

set analysis

marcel_olmo — Tue, 28 Dec 2010 17:20:56 GMT

Hey Roeland, as far as I can see in your example, there is a synthetic key with Banknr and Id fields.

If I were you, the first thing you have to do is to get an unique key like "banknr-Id".

After this, I think you should try with the aggr() function. Is very useful in this cases.

Try something like :

aggr(sum( {$< bank = {'$(=only(bank)'}>} loan),Customer).

This will calculate all the customers that have a loan with the selected bank.

Hope this helps!!!

set analysis

Wed, 29 Dec 2010 00:21:05 GMT

Hello Roeland,

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%.

Regards,

Roland (without e)

set analysis

Wed, 29 Dec 2010 17:04:29 GMT

Thanks for your input guys 🙂

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...

attached is his offered solution.