Dynamic KPI creation

Data comes in all shapes and sizes and sometimes, the users of the data want to see this as a series of metrics eg as key performance indicators. As always, there are not of ways of handling this and for a quick way in Qlik Sense you can use some generic logic to build a KPI data set that you can keep expanding.

This example draws some sample definitions from an inline table and then builds a KPI fact table that could potentially draw data from a lot of different sources (Finance, Personnel, Sales, etc).

We have used it where we didn’t have the time, budget and/or support for building this at the database level.

// Sample script for creating a series of KPI values

// Sample definitions, these could be imported in from a spreadsheet or database table as a more flexible alternative
// Sample data referred to is created using the sample Qlik script (Ctrl + 00) in Qlik Sense
KPIDefinitions:
Load * Inline [
	KPI Id, KPI Description, Numerator, Numerator Operator, Denominator, Denominator Operator, Filter, Format, Target, Target Comparison, Target Direction
	KPI-1, Expression2 as % of Expression3 (Dim1=A or C), Expression3, Sum, Expression2, Sum, "Where Wildmatch(Dim1, 'A*', 'C*')","0.00%", 1, ">=", Above
	KPI-2, Count of Dim1 (where Dim1=A), Dim1, Count, 1, , Where Dim1='A', "#,##0", 1000, "<", Below
	KPI-3, Average of Expression3, Expression3, Avg, 1,,,"#,##0.00", 3.5, ">", Above
]; 

// Create the KPI records using the rules and the raw data

// Work out the number of iterations by counting the KPIs
Let vKPIs = FieldValueCount('KPI Id');

// Create each KPI from the Transactions table
For i = 1 to $(vKPIs)
	vKPIId 		= Peek('KPI Id', $(i)-1, 'KPIDefinitions');
	vKPIDesc 	= Peek('KPI Description', $(i)-1, 'KPIDefinitions');
	vKPINum 	= Peek('Numerator', $(i)-1, 'KPIDefinitions');
	vKPINumOp 	= Peek('Numerator Operator', $(i)-1, 'KPIDefinitions');
	vKPIDen 	= Peek('Denominator', $(i)-1, 'KPIDefinitions');
	vKPIDenOp 	= Peek('Denominator Operator', $(i)-1, 'KPIDefinitions');
	vKPIFilter 	= Peek('Filter', $(i)-1, 'KPIDefinitions');
	vKPIFormat  = Peek('Format', $(i)-1, 'KPIDefinitions');
	vKPITarget = Peek('Target', $(i)-1, 'KPIDefinitions');
	vKPITargetComp = Peek('Target Comparison', $(i)-1, 'KPIDefinitions');
	vKPITargetDir = Peek('Target Direction', $(i)-1, 'KPIDefinitions');
		
	
	KPI:
	Load 
		Dim1,
		'$(vKPIId)'													as [KPI Id],
		'$(vKPIDesc)'												as [KPI Description],
		$(vKPINumOp)($(vKPINum))									as [KPI Numerator],
		$(vKPIDenOp)($(vKPIDen))									as [KPI Denominator],
		'$(vKPIFormat)'												as [KPI Format],
		$(vKPITarget)												as [KPI Target],
		'$(vKPITargetComp)'											as [KPI Target Comparison],
		'$(vKPITargetDir)'											as [KPI Target Direction]
		
		// the following are lines for debugging only so can remain commented unless there is an issue
		//,$(vKPINumOp)($(vKPINum)) / $(vKPIDenOp)($(vKPIDen))		as [KPI Value]
		//,'$(vKPIFilter)'											as [KPI Filter]
		//,'$(vKPIDenOp)($(vKPIDen))'									as [KPI Denominator Function]
		//,'$(vKPINumOp)($(vKPINum))'									as [KPI Numerator Function]		
	Resident Transactions
	$(vKPIFilter)
	Group By Dim1;
Next
	
// Tidy up by removing definitions table
Drop Table KPIDefinitions;