topic Re: Max Date in QlikView

Max Date

gerhardl — Wed, 15 Jun 2011 14:56:32 GMT

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

Max Date

pat_agen — Wed, 15 Jun 2011 15:39:54 GMT

hi Gerhard,

what are you looking at rate of approval of applications or number of accounts that have been approved? Are you at all interested in point of time analysis, ie will you pull data with a date of Monday and count the application/account as not being approved or will the analysis always be as from now, ie as of today (or the last time you loaded the data) how many accounts have been approved / total.

In the latter case in the script you should cretae an "account" table as opposed to the application table you have currently. In the account table you can establish a status with the latest (most recent) Decision Result. This will kmake your expressions simpler.

In the former case you will have to look at building a master calendar and linking that up with your accounts.

Max Date

Wed, 15 Jun 2011 16:39:25 GMT

Well, a very simple solution is possible, if all applications can only be approved once:

count( {$<status={Approved}>} application ) / count( distinct application )

Re: Max Date

gerhardl — Fri, 17 Jun 2011 07:41:56 GMT

Hi,

Sorry I thought that was working but something is not right. Please see the attached picture.

It is now countin the correct number of applications, but when I need to see the number of DECLINED apps it counts all declined apps, even if the same app was later approved.

When counting the declines, I need to only include the ones that were NOT later approved, so only the LATEST decision result must be used.

count({$<[Decision Result]={'Declined'}>} distinct [Appl No])

In the attached picture you can see there should only be 1 declined application, because one of the declines was later approved.

Re: Max Date

gerhardl — Fri, 17 Jun 2011 07:48:57 GMT

Can I add the distinct function to my script and only LOAD an application number once - and only load the latest one?

Currently the application data in my script looks like this:

LOAD if([Company No]='SCSA','Shoe City SA', if([Company No]='HTSA','Hang Ten SA', if([Company No]='ASA','Ackermans SA', if([Company No]='ANA','Ackermans NAM' )))) as [Company No],

[Product No],

[Product Type],

[Appl No],

Date(Date#( left( [Log Date],11), 'DD-MMM-YYYY')) as [Log Date],

Date(Date#( left( [Decision Date],11), 'DD-MMM-YYYY')) as [Decision Date],

if([Decicion Result]='A',[Appl Score]) as [Appl Score Approved],

if([Decicion Result]='D',[Appl Score]) as [Appl Score Declined],

ETC.

FROM
[.......\Application Extracts\Daily_ApplicationExtract_A_*.txt]
(txt, codepage is 1252, embedded labels, delimiter is '~', no quotes)

where ([Product No]>4);

So if an [Appl No] appears more than once, I only want to load the one where the [Decision Date] is the latest.

Thanks,

Re: Max Date

Fri, 17 Jun 2011 07:49:02 GMT

Well that depends on what you want to know.

If you want the number of application not approved you could do:

( count( distinct application ) - count( {$<status={Approved}>} application ) ) / count( distinct application )

Re: Max Date

Fri, 17 Jun 2011 07:55:01 GMT

Sorry just saw in your post you dont want percentages anymore the number of applications approved and rejected. So maybe try:

Approved: count( {$<status={Approved}>} application )

Declined: count( distinct application ) - count( {$<status={Approved}>} application )

Re: Max Date

gerhardl — Fri, 17 Jun 2011 07:56:24 GMT

I want to know a lot of things - I do demographical analysis and transactional reporting and have 5 sheets of tables and charts based on the applications.

All I want is for any application to only be counted (loaded) once - and the LATEST must be used.

So application 1234 might appear in the application extract for 12 June and be declined. Then it will appear again in the extract for 14 June and be approved.

So they will have the same app number, the same LOG date, but different DECISION dates and different DECISION RESULTS.

I do not want to know about the 12th june application, I want nothing to do with it, I only want the latest one as part of my data and calculations.

Max Date

gerhardl — Fri, 17 Jun 2011 08:01:53 GMT

I concatenate the new application extract every day with the older ones.

So basically if an [Appl No] already exists, I want to overwrite it with the new day's data.

Max Date

gerhardl — Fri, 17 Jun 2011 08:09:12 GMT

That fixes the number of declined applications. It now does not count the ones that were later approved.

But when I use [Decision Result] as a DIMENSION, it still returns the same, incorrect value.

I need to do this to show the % of approved/declined next to the amount.

So my expression is just

Count( distinct [Appl No]) with [Decision Result] as dimension.

Sorry for all the posts - I really appreciate your help!

Re: Max Date

Fri, 17 Jun 2011 08:23:14 GMT

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 )

Re: Max Date

gerhardl — Fri, 17 Jun 2011 08:36:10 GMT

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

Re: Max Date

Fri, 17 Jun 2011 08:51:48 GMT

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;

Re: Max Date

gerhardl — Fri, 17 Jun 2011 09:15:07 GMT

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,