topic Re: xor schema to solve distinct kpi/expression problem in QlikView

xor schema to solve distinct kpi/expression problem

Wed, 23 Oct 2013 07:36:14 GMT

Let's say we have n KPIS and each of them has 1 different expression and we want to place them in a table like this:

KPI Name KPI Value

KPI 1 Value 1

KPI 2 Value 2

KPI 3 Value 3

KPI 4 Value 4

KPI N Value n

would it be possible to make a high performance expression like:

FUNCTION_1( {<KPI_ID='1'>} KPI_VALUE1)

FUNCTION_2 {<KPI_ID='1'>} KPI_VALUE2)

FUNCTION_3( {<KPI_ID='1'>} KPI_VALUE3)

FUNCTION_4( {<KPI_ID='1'>} KPI_VALUE4)

....

FUNCTION_5( {<KPI_ID='N'>} KPI_VALUEN)

SO ONLY ONE EXPRESSION AT ONE TIME HAS THE DESIRED VALUE.?

Exist the null problem we cant sum a + null = null

Any solution?

Re: xor schema to solve distinct kpi/expression problem

Gysbert_Wassenaar — Wed, 23 Oct 2013 08:43:19 GMT

use rangesum instead of +: rangesum( function_1, function_2, ...etc)

Or perhaps you want to use the alt function that will return the first numeric result: alt(function_1,function_2, ....etc, 0)

Re: xor schema to solve distinct kpi/expression problem

Wed, 23 Oct 2013 11:46:34 GMT

do you think performance will be improved against a structure like :

pick ( match(KPI_ID,kpiname1,kpiname2,...................................................,kpinamen

,expression kpi 1

,expression kpi 2

,expression kpi 3

)

Re: xor schema to solve distinct kpi/expression problem

Gysbert_Wassenaar — Wed, 23 Oct 2013 12:06:11 GMT

Maybe, maybe not. The only way to make sure is to test both options. You can look at the CalcTime in the objects table on the Objects tab on the properties window of the sheet to see if it matters.