Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the following Expression for calculating Approval Rate of Applications:
(count({$<[Decision Result]={'Approved'}>}[Appl No]))/count([Appl No])
The problem is that an application will sometimes be declined due to missing information, then approved at a later stage - with the same account number. But because this will be on different dates, QlikView counts this as separate applications. So if application A is declined on Monday and then approved on Tuesday, it will show the approval rate as 50%, but it should be 100% because it is one application.
So I need to change the above expression to look at the field [Decision Date] and only include the LATEST decision date in the calculation. I guess I have to incorporate a maximum date function, but I dont know how - can someone please help??
Thanks,
Gerhard
Hi Jeroen,
I'm really sorry, I am still figuring all of this stuff out, and I do not understand what you mean.
Where should I use the sum expression?
What I REALLY want to do is change my load script to only import unique application number, and only the ones with the latest decision reslut.
That way I will not have to use any of these distinct expressions, I can just go on using my normal expression that I already have in place.
So load the same [Appl No] only once - always the one from the latest application extract or with the latest decision result.
I'm so confused...
Well that's the problem with getting your expressions accurate, they get harder to understand. That's why I prefer simple solutions like the first ones I posted.
The first if() returns the value 1is the [decision result] equals approved. You can just Sum() these 1's.
If you want to change your load script you could do something like:
LAST_APPLICATION:
LOAD application as LA_application, Max([decision date]) as LA_decision_date
RESIDENT APPLICATION
GROUP BY application;
INNER JOIN
LOAD application as LA_application, [decision date] as LA_decision_date, ... other fields
RESIDENT APPLICATION;
Wow - I really am starting to think I am too dumb for qlikview. Granted, I have never studied IT, I've never done any coding or SQL in my life, so this is all greek to me - I have to figure it out as I go.
I know I've already asked too much of you - but I cannot seem to get it right. I have attached a word file with my load script. And I have highlighted the fields that are relevan to this.
If you could just edit the script so only the applications with the latest decision date is loaded, I would very very much appreciate it. I'm afraid I do not understand where in my script to type the above - I keep getting errors.
Thank you so much for your patience.
Regards,
G