Central KPI Formula Repository (solution)

ChristofSchwarz — Tue, 22 Jul 2025 17:59:49 GMT

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.

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

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:

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

AggGroups are read
Set_Definitions are read in the sequence of SORTID
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"

	APPGROUP	APP_PATTERNNAME
2	MGMT	Management
3	MGMT	Dashboard
4	TEST	test
5	TEST	try

Table "Set_Definitions"

	USAGE	NAME	FORMULA
10	*	CY	Year={$(=Max(Year))}
20	MGMT	LH	CARRIER_ALN_CD_LH_Flag = {1}
30	*	PY	Year={$(=Max(Year)-1)}

Table "KPI_Definitions"

	USAGE	NAME	FORMULA	DONTRESOLVE	NUMFORMAT	DECIMALSEP	THOUSANDSEP
10	*	_PAX_NR	Sum ({$} PAX_NR)		0.000	,	.
20	*	_Destinations	Count(DISTINCT {< $(LH) >} OTHER_ARP_CD)
30	*	_%PAX	Sum(PAX_NR_DETAILS)/ SUM (TOTAL PAX_NR_DETAILS)		##0 %
40	MGMT	_Margin 1	sum(Margin_ONB)
50	*	_Margin 1 per PAX	$(_Margin 1) / $(_PAX_NR.LH)	1

Table "KPI_Variants"

	USAGE	NAME	VARIANTOF	SEARCH	REPLACE	DONTRESOLVE
10	*	_PAX_NR.CY	_PAX_NR	{$}	{< $(CY) >}	No
20	*	_PAX_NR.PY	_PAX_NR	{$}	{< $(PY) >}	No
30	*	_PAX_NR.LH	_PAX_NR	{$}	{< $(LH) >}	No

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.

Re: Central KPI Formula Repository (solution)

gardenierbi — Tue, 29 Sep 2015 12:50:30 GMT

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

Re: Central KPI Formula Repository (solution)

Anonymous — Tue, 29 Sep 2015 17:29:20 GMT

Please Mr.Schwarz:

Fix the images; It can be seen.

CB.

Re: Central KPI Formula Repository (solution)

ecolomer — Mon, 12 Oct 2015 03:33:30 GMT

Good work

Thank's for sharing.

Re: Central KPI Formula Repository (solution)

ChristofSchwarz — Sat, 17 Oct 2015 07:14:29 GMT

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

Re: Central KPI Formula Repository (solution)

Mon, 07 Dec 2015 22:00:13 GMT

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

Re: Central KPI Formula Repository (solution)

alexdataiq — Wed, 03 Feb 2016 18:14:41 GMT

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

Regards

Re: Central KPI Formula Repository (solution)

ChristofSchwarz — Thu, 04 Feb 2016 06:59:59 GMT

‌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.

Re: Central KPI Formula Repository (solution)

pwagner — Thu, 02 Mar 2023 10:20:06 GMT

@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?

topic Re: Central KPI Formula Repository (solution) in Archived Groups

Central KPI Formula Repository (solution)

Using a KPI File (CSV, Excel)

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

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

Using a KPI Database

Table "AppGroups"

Table "Set_Definitions"

Table "KPI_Definitions"

Table "KPI_Variants"

Script for QlikView

Script for Qlik Sense

The columns explained in detail

Traps avoided when using my script

Known limitations

Re: Central KPI Formula Repository (solution)

Re: Central KPI Formula Repository (solution)

Re: Central KPI Formula Repository (solution)

Re: Central KPI Formula Repository (solution)

Re: Central KPI Formula Repository (solution)

Re: Central KPI Formula Repository (solution)

Re: Central KPI Formula Repository (solution)

Re: Central KPI Formula Repository (solution)