Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

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?

0 Replies