Calculated KPI's as part of the Load Script - Similar to Set Analysis

[Anonymous]

Hey guys,

I've got to output monthly KPI values as part of a load script so that another application can pickup the aggregated values. Therefore, I need to do KPI calculations within the Load Script, rather than Set Analysis in the Qlik Sense application.

I am of the understanding that Set Analysis is not possible in the Load Script.

So the question is - how do I calculate a derived % (e.g. "Rejection Rate %' , defined by 'Total Refunds Rejected / (Total Refunds Approved + Rejected)' as part of the Load Script?:

Here is where I start:

1. The Refund Rejections and Authorisations come from different source tables. Therefore, I get their unique counts which need to be captured:

__________________________________________________________________________
Refunds_MS_Authorisations:
Load *,
ApplyMap('Mapping_Month', Authorisation_Status_Date) as YearMonth
FROM '$(vL.QVDSourcePath)Refunds_MS_Authorisations.qvd' (qvd);

Tmp_KPI_MS_Refunds_Approved:
Load
YearMonth,
count(Distinct(Batch_No)) as VolRefundsApproved,
num(sum(Transaction_Amount), '$#,##0.00') as TransactionTotal
RESIDENT Refunds_MS_Approved
WHERE Transaction_Type<>'APP CR'
Group By YearMonth
Order By YearMonth ASC;

Tmp_KPI_MS_Refunds_Rejected:
Load
YearMonth,
count(Batch_No) as VolRefundsRejected
RESIDENT Refunds_MS_Rejections
Group By YearMonth
Order By YearMonth ASC;

__________________________________________________________________________

2. This first part gets me the volumes. Then, I put them into a KPI Table (as I will drop the tables that appear above later on).

//************************************Store KPI's in unified monthly  Format ************************************************//
Tmp_KPI_MS_Refunds:
Load  'Total Refunds Approved' as KPINAME, VolRefundsApproved as KPIRESULT RESIDENT Tmp_KPI_MS_Refunds_Approved; //KPI Total Refunds Approved
Concatenate Load 'Total $$ Refunded' as KPINAME, TransactionTotal as KPIRESULT RESIDENT Tmp_KPI_MS_Refunds_Approved; //KPI Total Refunds Approved
Concatenate Load 'Total Refunds Rejected' as KPINAME, VolRefundsRejected as KPIRESULT RESIDENT Tmp_KPI_MS_Refunds_Rejected; //KPI Total Refunds Approved

So the question is, how do I now calculate 'Rejection Rate %' 'Total Refunds Rejected / (Total Refunds Approved + Rejected) , ensuring that it uses the same YearMonth (e.g. 201705) in each of the respective calculations and stores them in the same monthly format?