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

[pat_agen]

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.

[#3]

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

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

[gerhardl]

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.

[gerhardl]

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,

G

[#6]

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 )

[#7]

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 )

[gerhardl]

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.

[gerhardl]

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.

[gerhardl]

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!