Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
ChristofSchwarz
Partner Ambassador
Partner Ambassador

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 = ;

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

SET vKPI_KeepTable = 1; 

$(must_include=);

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=);

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.
8 Replies
gardenierbi
Creator II
Creator II

Nice solution cschwarz‌! The images on this page aren't visible for use.

Anonymous
Not applicable

Please Mr.Schwarz:

Fix the images; It can be seen.

CB.

ecolomer
Master II
Master II

Good work

Thank's for sharing. 

ChristofSchwarz
Partner Ambassador
Partner Ambassador
Author

I fixed the images. Those were pointing to an Qlik internal resource before. Also published an update.

Not applicable

Nice work! Any update on enhancing this to create Measures in Qlik Sense Master Items?

alexdataiq
Partner - Creator III
Partner - Creator III

Wow! this seems very complete. I will try to implement it in a future project!

Regards

ChristofSchwarz
Partner Ambassador
Partner Ambassador
Author

‌working on the functionality to add also into the master items. The problem is that from the load script alone there is currently no way to create those master items, so it needs to be an outside piece of code (eg nodejs) or an extension, which i likely will write.

pwagner
Partner - Creator III
Partner - Creator III

@ChristofSchwarz - Thank you for the great explanation. 

Unfortunately it's not possible to download your zip-file from Dropbox. Could you upload it once again, please?