topic Set Analysis Question in QlikView

Set Analysis Question

Thu, 15 May 2014 17:07:45 GMT

Hi everyone,

I have an expression set up in a chart that works fine until I start drilling down on data, and I'm wondering if any of you can identify why this is happening. I'm assuming that it has to do with incorrect identifiers. Expression is shown below.

Sum({$<current_balance=P({1<loanType={'Type1'}, daysPastDue={">=0<=10"}>}current_balance)>}current_balance)/(Sum({$<current_balance=P({1<loanType={'Type1'}>}current_balance)>}current_balance))

What I am trying to do is calculate a % of current_balance by calculating the sum of current_balance of Type1 loans in the 0-10 daysPastDue range and dividing that by the sum of current_balance for all loans of that type. As I said above this works fine until I drill down on data. Is there any syntax that I need to change for this to work if I narrow down by other fields? Thank you.

Re: Set Analysis Question

Thu, 15 May 2014 17:12:35 GMT

Try:

Sum({$<current_balance=P({1<loanType={'Type1'}, daysPastDue={">=0<=10"}>}current_balance)/(Sum({$<current_balance=P({1<loanType={'Type1'}>}current_balance))

Re: Set Analysis Question

Thu, 15 May 2014 17:29:19 GMT

It's giving me an error in set modifier element function name when I use that expressions. Any Ideas on howto fix that?

Re: Set Analysis Question

Thu, 15 May 2014 17:30:20 GMT

Hi Matthew,

Did you try with a simpler expression (without the P function) and check if it can do the job, like this:

sum({$<loanType={'Type1'}, daysPastDue = {">=0<=10"}>} current_balance) /

sum({$<loanType={'Type1'}>} current_balance)

You may want to use the TOTAL qualifier in the second part if you want to ignore the dimension of your pivot and thus considering the total current balance for type 1.

Hope it helps.