How to avoid nested if conditions

[sunitha_chellaiah]

Hi All,

I am facing performance issue due to nested if condition. It is getting lot of time to load a chart.

We have used vizlib table where only single measure has been used. 

Please help to optimize the below mentioned code.

IF( GetFieldSelections(display_ou_forecasting) = 'All' OR GetSelectedCount(display_ou_forecasting) = 0
,
// FORMULA ################################################################################################
IF(GetSelectedCount(UOM) = 1 AND GetSelectedCount(LAGS) = 1,
IF( (
sum({$<lag_period={ "$(= GetFieldSelections(LAGS))" }>}
IF( GetFieldSelections(UOM) = 'CEU' , ABC,
IF( GetFieldSelections(UOM) = 'Selling' , DEF,
IF( GetFieldSelections(UOM) = 'Third' , HIJ,
IF( GetFieldSelections(UOM) = 'Fourth' , KLM,
)))) )
)
= 0 AND
sum(
IF( GetFieldSelections(UOM) = 'CEU' , 123,
IF( GetFieldSelections(UOM) = 'Selling' , 456,
IF( GetFieldSelections(UOM) = 'Third' , 789,
IF( GetFieldSelections(UOM) = 'Fourth' , 354,
)))) )
> 0,
'/0',
(
sum(
IF( GetFieldSelections(UOM) = 'CEU' , 123,
IF( GetFieldSelections(UOM) = 'Selling' , 456,
IF( GetFieldSelections(UOM) = 'Third' , 789,
IF( GetFieldSelections(UOM) = 'Fourth' , 354,
)))) )
/
sum({$<lag_period={ "$(= GetFieldSelections(LAGS))" }>}
IF( GetFieldSelections(UOM) = 'CEU' , ABC,
IF( GetFieldSelections(UOM) = 'Selling' , DEF,
IF( GetFieldSelections(UOM) = 'Third' , HIJ,
IF( GetFieldSelections(UOM) = 'Fourth' , KLM,
)))) )
)
),0)
// END ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

, IF( GetFieldSelections(display_ou_forecasting) = 'Under Forecasting'
,
IF(
(
sum( total <brand,affiliate>
IF( GetFieldSelections(UOM) = 'CEU' , 123,
IF( GetFieldSelections(UOM) = 'Selling' , 456,
IF( GetFieldSelections(UOM) = 'Third' , 789,
IF( GetFieldSelections(UOM) = 'Fourth' , 354,
))))
)
/
sum(total <brand,affiliate> {$<lag_period={ "$(= GetFieldSelections(LAGS))" }>}
IF( GetFieldSelections(UOM) = 'CEU' , ABC,
IF( GetFieldSelections(UOM) = 'Selling' , DEF,
IF( GetFieldSelections(UOM) = 'Third' , HIJ,
IF( GetFieldSelections(UOM) = 'Fourth' , KLM,
))))
)
)
> kpi_target_r_ou_high
,
// FORMULA ################################################################################################
IF(GetSelectedCount(UOM) = 1 AND GetSelectedCount(LAGS) = 1,
IF( (
sum({$<lag_period={ "$(= GetFieldSelections(LAGS))" }>}
IF( GetFieldSelections(UOM) = 'CEU' , ABC,
IF( GetFieldSelections(UOM) = 'Selling' , DEF,
IF( GetFieldSelections(UOM) = 'Third' , HIJ,
IF( GetFieldSelections(UOM) = 'Fourth' , KLM,
)))) )
)
= 0 AND
sum(
IF( GetFieldSelections(UOM) = 'CEU' , 123,
IF( GetFieldSelections(UOM) = 'Selling' , 456,
IF( GetFieldSelections(UOM) = 'Third' , 789,
IF( GetFieldSelections(UOM) = 'Fourth' , 354,
)))) )
> 0,
'/0',
(
sum(
IF( GetFieldSelections(UOM) = 'CEU' , 123,
IF( GetFieldSelections(UOM) = 'Selling' , 456,
IF( GetFieldSelections(UOM) = 'Third' , 789,
IF( GetFieldSelections(UOM) = 'Fourth' , 354,
)))) )
/
sum({$<lag_period={ "$(= GetFieldSelections(LAGS))" }>}
IF( GetFieldSelections(UOM) = 'CEU' , ABC,
IF( GetFieldSelections(UOM) = 'Selling' , DEF,
IF( GetFieldSelections(UOM) = 'Third' , HIJ,
IF( GetFieldSelections(UOM) = 'Fourth' , KLM,
)))) )
)
),0)
// END ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
, 0
)
,
IF( GetFieldSelections(display_ou_forecasting) = 'Over Forecasting'
,
IF(
(
sum( total <brand,affiliate>
IF( GetFieldSelections(UOM) = 'CEU' , 123,
IF( GetFieldSelections(UOM) = 'Selling' , 456,
IF( GetFieldSelections(UOM) = 'Third' , 789,
IF( GetFieldSelections(UOM) = 'Fourth' , 354,
))))
)
/
sum(total <brand,affiliate> {$<lag_period={ "$(= GetFieldSelections(LAGS))" }>}
IF( GetFieldSelections(UOM) = 'CEU' , ABC,
IF( GetFieldSelections(UOM) = 'Selling' , DEF,
IF( GetFieldSelections(UOM) = 'Third' , HIJ,
IF( GetFieldSelections(UOM) = 'Fourth' , KLM,
))))
)
)
< kpi_target_r_ou_low
,
// FORMULA ################################################################################################
IF(GetSelectedCount(UOM) = 1 AND GetSelectedCount(LAGS) = 1,
IF( (
sum({$<lag_period={ "$(= GetFieldSelections(LAGS))" }>}
IF( GetFieldSelections(UOM) = 'CEU' , ABC,
IF( GetFieldSelections(UOM) = 'Selling' , DEF,
IF( GetFieldSelections(UOM) = 'Third' , HIJ,
IF( GetFieldSelections(UOM) = 'Fourth' , KLM,
)))) )
)
= 0 AND
sum(
IF( GetFieldSelections(UOM) = 'CEU' , 123,
IF( GetFieldSelections(UOM) = 'Selling' , 456,
IF( GetFieldSelections(UOM) = 'Third' , 789,
IF( GetFieldSelections(UOM) = 'Fourth' , 354,
)))) )
> 0,
'/0',
(
sum(
IF( GetFieldSelections(UOM) = 'CEU' , 123,
IF( GetFieldSelections(UOM) = 'Selling' , 456,
IF( GetFieldSelections(UOM) = 'Third' , 789,
IF( GetFieldSelections(UOM) = 'Fourth' , 354,
)))) )
/
sum({$<lag_period={ "$(= GetFieldSelections(LAGS))" }>}
IF( GetFieldSelections(UOM) = 'CEU' , ABC,
IF( GetFieldSelections(UOM) = 'Selling' , DEF,
IF( GetFieldSelections(UOM) = 'Third' , HIJ,
IF( GetFieldSelections(UOM) = 'Fourth' , KLM,
)))) )
)
),0)
// END ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
, 0
)
)))

//=$(vO/U KPI %_Forecast_Bias_DFU_Brand&Affiliate)