xor schema to solve distinct kpi/expression problem

[#1]

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?

[Gysbert_Wassenaar]

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)

[#3]

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

)

[Gysbert_Wassenaar]

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.