Central KPI Formula Repository (solution)

    Current Version 3.20 (16-Oct-2015)
    Now supports Partial Reload, runs faster with large data sets
    and puts Num() function only on the outer KPI if KPIs are nested.

    Download: Dropbox - KPI_Repository.zip


    I recommend to name KPI variables starting with "_". Then you have a nice way to use type ahead formula completion in Sense and QlikView, as all your KPIs sort in a dropdown list next to the "_".

    As Presales, you have probably done it a hundred times: Add lots of KPIs formulas and also try to keep a maintainable format with a "define once" principle. Formulas and Sets should be separated in their definitions and mixed and reused as needed.

    This solution allows you to

    • Define KPIs separate from the Apps and define in which app to use the KPI
    • Make Set-Definitions separately from KPIs
    • Use KPIs on basis of other KPIs (KPI3 = KPI1 / KPI2)
    • Inject Set-Modifiers in the same KPI creating variants of the KPI without redefining.
    • In large apps you will need just a Partial Reload to redefine formulas, not a entire data loading. The script runs also in Partial Reload mode.
      (for Sense 2.0 and 2.1 you need this external tool to execute a Partial Relaod https://community.qlik.com/docs/DOC-8866)

    It is common knowledge that

    • you can put your KPI formulas into a text file or  in a database tables
    • load this table and loop through it while creating variables for each formula
    • Anywhere in Qlik Sense or QlikView you may then call that formulas by the dollar-brackets syntax $(my_variable) ... this is a "code injection" interpreted ad-hoc.

    But wait a minute! There are two traps, which are definately NOT common knowledge.

    • Those dollar-bracket $(constructs) are immediately interpreted in the script during script execution. This makes a difference and often results in undesired variable content.
    • The combination dollar-brackets-equal $(= ...) always leads to a hard to spot error. The script will not throw an error, but instead of where you had $(=something) you will find $(internal error) or simply anything removed between "$(" and ")".

    So forget this manual approach and use my include script in one of the below ways: minimalistic flat file, maximum flat file, or database mode.

    Using a KPI File (CSV, Excel)

    Any textformat is supported. You have to specify the arguments for the text format.

    Clipboard02.png


    Alternatively, use this in an Excel sheet. Same thing, easier to edit, but Excel is not always available during a SiB

    Clipboard01.png

    Clipboard03.png

    If the KPI Definitions are in a Flat file and you use QlikView:

    SET vKPI_Source = 'File';

    SET vKPI_File = [E:\include\KPI_Def.txt];

    SET vKPI_File_Params = [txt, codepage is 1252, embedded labels, delimiter is '\t', msq];

    SET vKPI_KeepTable = 1; 

    $(must_include=[E:\include\Create_KPI_Repository-include.txt]);

    If the KPI Definitions are in a Flat file and you use Qlik Sense:

    Create a folder connection to where the include-script.txt is placed and a folder connection (if different) where the KPI definition file is placed. All formats of the file are supported but you have to provide the file import parameters below, so it could be txt, biff, xlsx …

    SET vKPI_Source = 'File';

    SET vKPI_File = [lib://include/KPI_Def.txt]; // Qlik Sense

    SET vKPI_File_Params = [txt, codepage is 1252, embedded labels, delimiter is '\t', msq];

    SET vKPI_KeepTable = 1;

    $(must_include=[Lib://include/Create_KPI_Repository-include.txt]);

    Using a KPI Database

    It doesn't matter which database you used. Attached is an MS Access example. Could be any other relational database.You will need 4 tables:

    Screenshot 2015-09-16 17.36.05.png

    You know, sequence matters, so it is key to understand when you can already refer to a "previously" defined Formula.The sequence when using a database is like this

    1. AggGroups are read
    2. Set_Definitions are read in the sequence of SORTID
    3. KPI_Definitions and KPI_Variants are cascaded together, the main order is driven by the SORTID of KPI_Defintions, but whenever a KPI_Variant refers to each given KPI_Definition it immediately defines the dependent KPI_Variants after its parent KPI_Definition.

    Examples for each table

    Table "AppGroups"

    2MGMT*Management*
    3MGMT*Dashboard*
    4TEST*test*
    5TEST*try*
    APPGROUPAPP_PATTERNNAME

    Table "Set_Definitions"

    10*CYYear={$(=Max(Year))}
    20MGMTLHCARRIER_ALN_CD_LH_Flag = {1}
    30*PYYear={$(=Max(Year)-1)}
    USAGENAMEFORMULADONTRESOLVECOMMENT

    Table "KPI_Definitions"

    10*_PAX_NRSum ({$} PAX_NR)0.000,.
    20*_DestinationsCount(DISTINCT {< $(LH) >} OTHER_ARP_CD)
    30*_%PAXSum(PAX_NR_DETAILS)/ SUM (TOTAL PAX_NR_DETAILS)##0 %
    40MGMT_Margin 1sum(Margin_ONB)
    50*_Margin 1 per PAX$(_Margin 1) / $(_PAX_NR.LH)1
    USAGENAMEFORMULADONTRESOLVENUMFORMATDECIMALSEPTHOUSANDSEPCOMMENT

    Table "KPI_Variants"

    10*_PAX_NR.CY_PAX_NR{$}{< $(CY) >}No
    20*_PAX_NR.PY_PAX_NR{$}{< $(PY) >}No
    30*_PAX_NR.LH_PAX_NR{$}{< $(LH) >}No
    USAGENAMEVARIANTOFSEARCHREPLACEDONTRESOLVENUMFORMATDECIMALSEPTHOUSANDSEPCOMMENT

    Note that KPI_Variants has no FORMULA column, as the formula is inherited from the KPI defined under VARIANTOF.

    Script for QlikView

    //Put your database connector here

    OLEDB CONNECT TO [Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=.............];

    SET vKPI_Source = Database;

    LET vKPI_SQL_AppGroups = 'AppGroups';

    LET vKPI_SQL_Set_Definitions = 'Set_Definitions';

    LET vKPI_SQL_KPI_Definitions = 'KPI_Definitions';

    LET vKPI_SQL_KPI_Variants = 'KPI_Variants';

    $(must_include=[E:\include\Create_KPI_Repository-include.txt]);

    Script for Qlik Sense

    Create a folder connection to where the include script is placed. In below example that connection is called “include” Create the database connection to where the definition table Is located. Connect to it, then provide the SQL command in the variable vKPI_Select before calling the include script.

    LIB CONNECT TO 'kpi_definitions_db';

    SET vKPI_Source = Database;

    LET vKPI_SQL_AppGroups = 'AppGroups';

    LET vKPI_SQL_Set_Definitions = 'Set_Definitions';

    LET vKPI_SQL_KPI_Definitions = 'KPI_Definitions';

    LET vKPI_SQL_KPI_Variants = 'KPI_Variants';

    $(include=[Lib://include/Create_KPI_Repository-include.txt]);

    The columns explained in detail


    Provide columns in UPPER CASE. The sequence of the columns doesn't matter. You don't have to provide all columns, the 3 mandatory columns are enough. Any other combination is allowed.


    USAGE (mandatory)work in progressNote: the script will not “find” apps with a matching title anywhere and inject the KPI definitions there. You need to add the load script yourself in any of the applicable apps.Be aware that a formerly created variable is not removed if later a wildcard expression is changed and is no longer applicable for a given app. The variable remains with its last loaded state unless removed in the frontend.


    NAME (mandatory)Variable name under which the formula is stored. Brackets and single quotes are not allowed in the KPI_Name, the name is case-sensitive.Recommended characters: A-Z, a-z, 0-9, @, $, _ and space. Use a distict prefix for sets (like set_ or $) and another prefix for KPI formulas


    FORMULA (mandatory)Put the correct formula or other definitions here.Altough you can assign all type of content into variables here, I see two typical ways of usage: Aggregation Formulas (aka "KPI") and Set Modifiers

    • Important: The formula can hold a reference to a previously defined formula in the dollar-brackets-syntax: $(reference).
    • Unless column “DONTRESOLVE” is set to 1, the reference is immediately resolved (replaced with the content) upon script execution. If this is not wanted and the reference should make it until later use in the GUI (frontend), put the column “DontResolve” to 1
    • If you used $(= … fragment …) combination in the formula (watch for the equal sign(!) after dollar-open bracket) this fragment will not be interpreted by the script execution but passed into the variable definition as part of the formula.  E.g. Sum({< Year={$(=Year(Now())}>} Amount)


    VARIANTOF (optional)work in progress


    SEARCH (optional)work in progress


    REPLACE (optional)work in progress


    DONTRESOLVE (optional)A flag that defines whether a formula with a dollar-brackets $(reference) is resolved (=replaced by it’s referred content) immediately during Script execution or if the reference (the dollar-brackets fragment) is kept as part of the formula itself.

    • put 1 to NOT resolve dollar-bracket $(fragments) ... if no such  fragment is used, 1 has no effect
    • put 0 to resolve dollar-bracket $(fragments)
    • If omitted, 0 is assumed.


    NUMFORMAT (optional)Optionally, put a valid format-string here. That will cause the above formula to be wrapped into a NUM( … ) formula, where columns 6 and 7 are used as additional parameters for the NUM() formula.

    • If omitted, no NUM( … ) formula will be used and formatting is up to the format of the data or chart default.
    • Typical examples: 0.0%  #.##0  #,##0.00    #,##0.00 €


    DECIMALSEP(optional)If column 5 is used, this may optionally define the decimal separator (one character only).If NumFormat is not used, this column is ignored


    THOUSANDSEP(optional)If column 5 and 6 are used, this may optionally define the thousand separator (one character only).If DecimalSep is not used, this column is ignored. It only works in combination with columns 5 + 6

    Traps avoided when using my script


    If you would like to understand why defining KPIs in variables directly in the script will be problematic then try this in your Load Script

    • LET set_LastYear = 'Year={$(=Year(Now())-1)}';
    • See the content of the variable "set_LastYear"? It will read 'Year={}' and the lovely formula =Year(Now())-1 is gone. In earlier versions of QlikView you may read 'Year={(internal error)}' instead.
    • Try the same with SET:
    • SET set_LastYear = 'Year={$(=Year(Now())-1)}';
    • No difference. As a general rule

    So we conclude as a general ruleIconThe only way to define those formulas in a way to survive the script run and to make it into the frontend use, you have to either use my template or separate the dollar from the open brackets. Avoid the two characters unless you want the script immediately to inject something at this point. Try this:

    • LET set_LastYear = 'Year={$' & '(=Year(Now())-1)}';  // the string is torn into two substrings and concatenated with &
    • LET set_LastYear = Replace('Year={§(=Year(Now())-1)}', '§', '$');  // used § instead of $ in the formula and later replaced it

    In both ways, the script doesn't "see" the dollar in combination with open-brackets. It will do nothing here but to save the result of the string operation (concatenation or Replace) into the variable. Then you will get what you wanted and in the GUI you can check, that the variable set_LastYear finally contains 'Year={$(=Year(Now())-1)}'You don't want to expose this to your customer in a SiB, do you? So use my simple template instead ...

    Known limitations

    • Defining the KPI formulas only prepares variables for later use. It does NOT create Measures in the Qlik Sense Master Items
    • Measures which are removed in the definitions won't be deleted from the apps which were using it. Be careful with name-giving of those KPI variables, as they will remain in the apps. If you change the name the old ones will still be there together with the new.