Max Date

[gerhardl]

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

[#2]

Hi Gerhard,

Well first of all, if you really want to fetch the last [decision result] for each application, you can do something like:

sum(
if(
if (aggr( NODISTINCT max(date), application) = date, [decision result] ) = 'Approved', 1
))

That will fetch the last decision result for any application.

Next problem is you want to use the [decision result] dimension. The problem is if you use that, the records selected for your calculations will be limited to your selection, so count( distinct application ) no longer gives you a count of all applications. You can ignore the selected dimension in your calculation with a set modifier <> as part of a set analysis {} :

count( {$<[decision result]=>} distinct application )

[gerhardl]

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

[#4]

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;

[gerhardl]

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