Custom KPI Report Using Excel Template and Variables

    I was sure I had seen a similar example of this on the forum somewhere but I couldn't find it. The closest I could get was the excellent P&L example here: How to Create a Profit and Loss Statement in QlikView  So sorry if this is going over old ground but I hope it is useful to someone.

     

    Introduction

     

    I recently had a specification to replicate an excel spreadsheet in Qlik.

    We've all been there and we all know that its a stupid idea but sometimes you just have to go with it.

    This was to go into a dashboard where the majority of the expressions required already existed in variables.

     

    What I needed to be able to do was replicate this KPI report in Qlikview, but I also wanted to keep the template in Excel (could just as easily be SQL) to make editing it easier.

     

    The report contained a whole bunch of disparate KPIs, about half of which were essentially pivoted over months and then given an average. The second half were not really related in any way, except some had both a value and a number column.

     

    I started by replicating all the little bits and pieces as separate tables, but this looked very messy and was also a pain to extract because the user had to extract each object (Ajax).

     

    This got me to the point of creating two report templates:

     

    1- A whole bunch of expressions which could be pivoted over months

    2- A set of unrelated expressions which needed custom headings at different sections

     

    The attached example is obviously very basic, the expressions don't really need to be in variables but they are to prove the fact that it works, the report outputs themselves are much more basic than the reality but hopefully it can give you a good starting point to develop from.

     

    Final Output

     

    As everyone wants to see what we are working towards right?

     

    Straight Table:

    Capture.PNG

     

    Pivot

    Capture.PNG

     

    Template

     

    The template file can be found below and includes two tabs, one for the pivot report and one for the straight report

     

    Pivot:

    Capture.PNG

    Straight Table:

    Capture2.PNG

     

    The input is very simple, the first column on both can have the values (and you could extend with your own) which control the formatting:

    h - Header row

    r - row

    b - blank row

     

    The second column is just the row number, which could easily be generated in Qlik but its nice to have in the spreadsheet while you're laying it all out

     

    Then you simply have calculation and format columns which dictate what will be shown in the various rows.

    Note that in the straight table, I have some text fields in the calculations columns for the header rows and the format is flagged as 't' which avoids applying a number format.

    Finally the format of 'kpi' is a keyword as it does some specific formatting.

     

    Load Script

     

    I've commented my load script in the attached file so you should be able to see what is going on.

    The key things are how the formula are built based on the 'type' of the row (b,r,h)

     

    Pick Match Formula

     

    These formula / variables are the key to the whole thing working. They're also built on the fly which means you don't have to fiddle with them, they're just built up from the spreadsheet.

     

    For example this is the formula used to created the pivot expression:

     

    'pick(match(_ReportpivotRow,'&Concat(chr(39)&_ReportpivotRow,chr(39)&',',_ReportpivotRow)&chr(39)&'),'&Concat(_ReportpivotExpression,',',_ReportpivotRow)&')'
    

     

    Which in the case of this example returns this beast:

    pick(match(_ReportpivotRow,'1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16')
    ,0,num($(v_exp_count_transactions),'#,##0'),num($(v_exp_value_transactions_value),'£#,##0;-£#,##0'
    ),num($(v_exp_value_transactions_value) / $(v_exp_count_transactions),'£#,##0;-£#,##0'),0,0
    ,num($(v_exp_count_cancelled_transactions),'#,##0')
    ,num($(v_exp_value_cancelled_transactions_value),'£#,##0;-£#,##0'),
    num($(v_exp_value_cancelled_labour_value),'£#,##0;-£#,##0')
    ,num($(v_exp_value_cancelled_materials_value),'£#,##0;-£#,##0'),0,0,
    num($(v_exp_value_hours_worked),'#,##0'),num($(v_exp_value_hours_budget),'#,##0'),
    num($(v_exp_value_hours_variance),'#,##0'),0)
    

     

    But as you can see, it has just built the formula up which means the correct formula from the setup will be executed against the correct line in the report.

     

    This is then executed in the chart like this:

     

    =if(columnNo()=0,if(_ReportpivotLevel='b'  or (_ReportpivotLevel='h' AND _ReportpivotExpression=0),'',$(=$(v_report_pivot_pickmatch_avg)))
      , if(_ReportpivotLevel='b' or (_ReportpivotLevel='h' AND _ReportpivotExpression=0),'',$(=$(v_report_pivot_pickmatch))))
    

     

    What this is doing is checking if the column is 0 (i.e. the total column) and then applying the correct formula.

    In this case if it is our total column then we're forcing it to calculate an average rather than the usual pivot total.

     

    If the line is a blank line, or a header row with no calculation then set the value to blank

     

    Otherwise then execute our normal pick match.

     

     

    The straight table is very similar, we just have four pickmatch variables and no total one, for example:

     

    = if(_ReportstraightLevel='b' or  _ReportstraightExpression1=0,'',$(=$(v_report_straight_pickmatch1)))
    

     

     

    Formatting

     

    There isn't a huge amount of trickery going on really, things are just formatted depending if they're a header row or a KPI row

    The row numbers are just formatted to be white

    The header dimension is made bold if it is a header row:

    =if(_ReportstraightLevel='h','<b>')
    

     

    The straight table has a bit more formatting on the expressions to make the arrows red or green depending if they're up or down:

     

    =if($(=$(v_report_straight_pickmatch1))='$(v_kpi_down_arrow)',red(),if($(=$(v_report_straight_pickmatch1))='$(v_kpi_up_arrow)',green()))
    

     

    Limitations

     

    1- You need to have the row number in the chart to make sure all rows show and for the pick match to work

         - Originally the pick match ran from the description but this meant you couldn't duplicate descriptions

         - row number is also required to ensure all rows show including the blank ones

     

    2- To calculate the average in the pivot, you need to hard code the dimensions into the Aggr formula

     

    3- Kpi flags / traffic lights are limited to what you can write and format as text in an expression