Set analysis performance optimization - reduce if function interaction

[davdiste]

Hi,

I'm developing an app caracterized by 4 level of variables, in which each upper level variables recall lower level ones. The measures included into the variables can't be developed in back end script. The upper level variables generate a long script visualized in expression information (see the attachment).

The issue we're facing is related with IF function  in expression, like that presented below, where an upper variable is evaluated several times within the IF generating a really long script and long lag in front end result visualization.

IF ($(score) >= SUM({<ID_PV_INDICE={'IPIMB'}>} POINTS),'Risk very low',
IF ($(score) > SUM({<ID_PV_INDICE={'IPIMA'}>} POINTS)
and $(score) <= SUM({<ID_PV_INDICE={'IPIA'}>} POINTS) ,'Risk high',
IF ($(score) > SUM({<ID_PV_INDICE={'IPIA'}>} POINTS)
and $(score) <= SUM({<ID_PV_INDICE={'IPIM'}>} POINTS) ,'Risk medium',
IF ($( score) <= SUM({<ID_PV_INDICE={'IPIMA'}>} POINTS) ,'Risk very high',
'Risk low'
))))

May anybody suggest an alternative function that enables better performance providing the variables "score" in expression just one time (like properties of the switch function in back end)?

Thanks, best regards.

Davide

[Kushal_Chawda]

may be this

Pick(match(-1,
$(score) >= SUM({<ID_PV_INDICE={'IPIMB'}>} POINTS),
$(score) > SUM({<ID_PV_INDICE={'IPIMA'}>} POINTS)
and $(score) <= SUM({<ID_PV_INDICE={'IPIA'}>} POINTS) ,
$(score) > SUM({<ID_PV_INDICE={'IPIA'}>} POINTS)
and $(score) <= SUM({<ID_PV_INDICE={'IPIM'}>} POINTS) ,
$( score) <= SUM({<ID_PV_INDICE={'IPIMA'}>} POINTS))+1,
'Risk low'
'Risk very low',
'Risk high',
'Risk medium',
'Risk very high',
)

[davdiste]

Thanks Kush, 

but also in the pick match function the variables $(score) is recalled several times and the interaction are similar at  IF the original function.

[Kushal_Chawda]

But it will be faster as compared to if condition. Please validate that

[rwunderlich]

Does $(score) need to be evaluated at each dimension value or can it be calculated over all data?  

-Rob

[rwunderlich]

Hi @Kushal_Chawda have you tested that pick/match is faster than if()? I have not found it to be faster.

-Rob

[davdiste]

Hi, at least need to be evaluated at each gerarchical dimension attribute. Thx

[Kushal_Chawda]

@rwunderlich in one of my requirement I found it to be slightly faster as compared to if . I am not sure but may be it will depend on what kind of expression you are comparing on how much data.