0 Replies Latest reply: Sep 22, 2017 12:11 AM by Peter Bower RSS

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

    Peter Bower

      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?