topic Help Newbie: need help with caluculation in QlikView

Help Newbie: need help with caluculation

Tue, 19 Apr 2011 18:53:11 GMT

I have two values LastPayment and NextPayment -these are both dates with the form mm-dd-yy

I need to show the the % of loans where Diff LastPayment and NextPayment > 30 days.

Thanks!!

Help Newbie: need help with caluculation

Tue, 19 Apr 2011 19:34:55 GMT

OK. Part way there. This is what I have so far...

num(month([Next Payment Due])) - num(month([Last Payment Due]))

Help Newbie: need help with caluculation

stevedark — Tue, 19 Apr 2011 19:59:29 GMT

Hi there,

The way I would approach this would be to add some flags in the load script, this will make your expressions simpler in the front end.

Something like:

Loans:
LOAD
1 as LoanCount,
if(NextPayment - LastPayment > 30, 1, 0) as Over30Count,
[... rest of table load ...]

Your expression is then simply:

SUM(Over30Count) / SUM(LoanCount)

Creating flags and count fields at load time substantially speeds up calculation times and also breaks up calculations into easier to manage chunks.

If you want to have a dynamic gap between the dates (perhaps on a slider) then you need to nest a if statement in the first sum, eg.:

SUM(if(NextPayment - LastPayment > $(vDaysDiff), LoanCount, 0)) / SUM(LoanCount)

Note that this still uses the '1 as LoanCount' in the load script.

Hope that helps.

Regards,
Steve

AW:Help Newbie: need help with caluculation

Tue, 19 Apr 2011 20:00:21 GMT

Calculate in your code, the difference between last and next payment and create thefield lastPay_30d


if((NextPayment-LastPayment) >= 30, 'Y','N') as lastPay_30d

Now you can create a ListBox from lastPay_30d. So can you sellect all your Customers with the Payment Difference.

The Payment difference you can make dynamic. You can do it with Set Analisys.

- QlikJam

AW:Help Newbie: need help with caluculation

johnw — Tue, 19 Apr 2011 23:53:09 GMT

Rather than 1/0 or Y/N flags (though use Y/N if you want it to be selectable), I'd use a 1/null flag, which I think would make for a more efficient sum.

if(NextPayment - LastPayment > 30,1) as Over30Count

If your percentage is supposed to be by LoanAmount instead of by count, you could do this:

sum({<Over30Count={1}>} LoanAmount)/sum(LoanAmount)

Or this:

if(NextPayment - LastPayment > 30,LoanAmount) as Over30Amount

sum(Over30Amount)/sum(LoanAmount)

AW:Help Newbie: need help with caluculation

stevedark — Wed, 20 Apr 2011 07:40:01 GMT

Hi John,

I tend to make a point of avoiding nulls where possible. On one site I had a client with 200 million rows in their document and replacing nulls with zeros made a huge difference to some of the calculations - I can not recall if the value in question was used in set analysis or the standard part of the expression. Needs some benchmarking to be sure.

The other reason I like the binary flag is that it can then be used in expressions - which I find useful with clients that have an aversion to section access, eg.:

sum(LoanAmount * Over30Count) / sum(LoanAmount)

My guess is that the above statement would perform slower than the set analysis version - but it is readable by someone with a phobia of curly brackets.

Regarding your point on being selectable, I sometimes have fields both ways - as we know that a field with only two values in takes virtually no memory even when on a massive table. This I tend to do with a preceding load:

LOAD
*,
ApplyMap('Map_Bool2YesNo', Over30Count) as [Is Over 30]
;
LOAD
[...]

I completely agree with your suggestion about adding an Over30Amount field into the data model - certainly the best way to make calculations on that more efficient. Again I tend to calculate the boolean in the main body of the load and then use this in a preceding load to create extra fields, eg.:

LOAD
*,
LoanAmount * Over30Count as Over30Amount

Many thanks for sharing your thoughts, John, always good to get ideas from an expert!

Regards,
Steve