Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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)
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
)
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.