Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
It is common knowledge that
But wait a minute! There are two traps, which are definately NOT common knowledge.
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.
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
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=
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]);
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
Examples for each table
2 | MGMT | *Management* |
3 | MGMT | *Dashboard* |
4 | TEST | *test* |
5 | TEST | *try* |
APPGROUP | APP_PATTERNNAME |
---|
10 | * | CY | Year={$(=Max(Year))} | ||
20 | MGMT | LH | CARRIER_ALN_CD_LH_Flag = {1} | ||
30 | * | PY | Year={$(=Max(Year)-1)} | ||
USAGE | NAME | FORMULA | DONTRESOLVE | COMMENT |
---|
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 | ||||
USAGE | NAME | FORMULA | DONTRESOLVE | NUMFORMAT | DECIMALSEP | THOUSANDSEP | COMMENT |
---|
10 | * | _PAX_NR.CY | _PAX_NR | {$} | {< $(CY) >} | No | ||||
20 | * | _PAX_NR.PY | _PAX_NR | {$} | {< $(PY) >} | No | ||||
30 | * | _PAX_NR.LH | _PAX_NR | {$} | {< $(LH) >} | No | ||||
USAGE | NAME | VARIANTOF | SEARCH | REPLACE | DONTRESOLVE | NUMFORMAT | DECIMALSEP | THOUSANDSEP | COMMENT |
---|
Note that KPI_Variants has no FORMULA column, as the formula is inherited from the KPI defined under VARIANTOF.
//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=
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]);
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
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.
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.
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
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
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:
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 ...
Nice solution cschwarz! The images on this page aren't visible for use.
Please Mr.Schwarz:
Fix the images; It can be seen.
CB.
Good work
Thank's for sharing.
I fixed the images. Those were pointing to an Qlik internal resource before. Also published an update.
Nice work! Any update on enhancing this to create Measures in Qlik Sense Master Items?
Wow! this seems very complete. I will try to implement it in a future project!
Regards
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.
@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?