Error Calculation Timed Out

[paolojolly]

Hi, 

I'm asking for help with a Calculation timed out issue that I have on a pivot table with the following  dimension 

=AGGR(

SUM( 

Aggr(

sum(

Aggr

(

sum(

if(DATA_MOV_CONS >= data_ini and DATA_MOV_CONS <= data_fin and C_CATEGORIA_ARTICOLO_CONS <> 'SLP' AND C_CATEGORIA_ARTICOLO_CONS <> 'PFI',

W_QTA_MOV_CONS

)

)

*

$(v_pm_periodo_imb)

,

KEYARTMERCBUD, C_ARTICOLO_RIF_OP_PF, C_ARTICOLO_CONS

)

)

, KEYARTMERCBUD,  C_ARTICOLO_RIF_OP_PF)

)

, KEYARTMERCBUD)

I don't know if it's possible to convert it in order to reduce the working time

Thank you in advance for your time and help

[Aasir]

Nested sometime does this, Try below
Aggr(
Sum(
if(
DATA_MOV_CONS >= data_ini and DATA_MOV_CONS <= data_fin and C_CATEGORIA_ARTICOLO_CONS <> 'SLP' AND C_CATEGORIA_ARTICOLO_CONS <> 'PFI',
W_QTA_MOV_CONS * $(v_pm_periodo_imb),
0
)
),
KEYARTMERCBUD, C_ARTICOLO_RIF_OP_PF, C_ARTICOLO_CONS
)

[marcus_sommer]

(nested) (inside aggregations) if-loops and (nested) aggr() are performance killer and should be in general avoided. Therefore I suggest to re-think the UI calculation and the entire data-model (recommended is a star-scheme) and all essential associations are done within the data-model.

Beside this you may transfer the column-level conditions of the if-loop into a set analysis and if row-level conditions remain to move them to the outside of the aggregation, means simplified something like:

...
if(RowLevel = 'Condition1', sum({< ColumnLevel = {'Condition2'}>} Field))
...

[vincent_ardiet_]

What is the nature of "data_ini" and "data_fin"? Are they variables or fields?

What contents "v_pm_periodo_imb"?

If they are variables, you should be able to modify the expression like this:

=Aggr(

sum(

Aggr

(

sum(
{<DATA_MOV_CONS={">=$(=data_ini)<=$(=data_fin)"},C_CATEGORIA_ARTICOLO_CONS-={SLP,PFI}>}

W_QTA_MOV_CONS

)

)

*

$(v_pm_periodo_imb)

,

KEYARTMERCBUD, C_ARTICOLO_RIF_OP_PF, C_ARTICOLO_CONS

)

)

, KEYARTMERCBUD)

[paolojolly]

Thanks for your reply. 

"data_ini" and "data_fin" are variables and v_pm_periodo_imb is a variabile too. In attached file you can find its definition.

I tried to put your expression in the pivot table, but I still have the same issue.

[paolojolly]

Thanks for your reply. 

Your solution works but doesn’t group data. My need is to multiply each C_ARTICLE_CONS with its cost coming from $(v_pm_periodo_imb) and then group these data by KEYARTMERCBUD

[vincent_ardiet_]

v_pm_periodo_imb contents too many if.
Try to replace it with this (not perfect but better):

IF(

SUM({<DATA_FATT_FORN_FATT_ACQ_IMB={">=$(=data_ini)<=$(=data_fin)">} W_IMPONIBILE_RIGA_FATT_ACQ_IMB) <> 0,

////////////////////////////////////PERIODO

SUM({<DATA_FATT_FORN_FATT_ACQ_IMB={">=$(=data_ini)<=$(=data_fin)">} W_IMPONIBILE_RIGA_FATT_ACQ_IMB)

/

SUM( {<DATA_FATT_FORN_FATT_ACQ_IMB={">=$(=data_ini)<=$(=data_fin)">} W_QTA_UM_FATT_ACQ_IMB)

////////////////////////////////////

,

IF(

SUM( {<DATA_FATT_FORN_FATT_ACQ_IMB={">=$(=YearStart(AddYears(data_ini,-1)))<=$(=YearEnd(AddYears(data_fin,-1)))">} W_IMPONIBILE_RIGA_FATT_ACQ_IMB) <> 0,

/////////////////////////////////////////////////////////////////////////////////////////// PERIODO -2

SUM({<DATA_FATT_FORN_FATT_ACQ_IMB={">=$(=YearStart(AddYears(data_ini,-1)))<=$(=YearEnd(AddYears(data_fin,-1)))">} W_IMPONIBILE_RIGA_FATT_ACQ_IMB)

/

SUM({<DATA_FATT_FORN_FATT_ACQ_IMB={">=$(=YearStart(AddYears(data_ini,-1)))<=$(=YearEnd(AddYears(data_fin,-1)))">} W_QTA_UM_FATT_ACQ_IMB)

////////////////////////////////////

,

if(

SUM({<DATA_FATT_FORN_FATT_ACQ_IMB={">=$(=YearStart(AddYears(data_ini,-2)))<=$(=YearEnd(AddYears(data_fin,-2)))">} W_IMPONIBILE_RIGA_FATT_ACQ_IMB)<>0,

SUM({<DATA_FATT_FORN_FATT_ACQ_IMB={">=$(=YearStart(AddYears(data_ini,-2)))<=$(=YearEnd(AddYears(data_fin,-2)))">} W_IMPONIBILE_RIGA_FATT_ACQ_IMB )

/

SUM({<DATA_FATT_FORN_FATT_ACQ_IMB={">=$(=YearStart(AddYears(data_ini,-2)))<=$(=YearEnd(AddYears(data_fin,-2)))">} W_QTA_UM_FATT_ACQ_IMB)

, 

////// -3 inizio

if(

SUM({<DATA_FATT_FORN_FATT_ACQ_IMB={">=$(=YearStart(AddYears(data_ini,-3)))<=$(=YearEnd(AddYears(data_fin,-3)))">} W_IMPONIBILE_RIGA_FATT_ACQ_IMB) <> 0,

SUM({<DATA_FATT_FORN_FATT_ACQ_IMB={">=$(=YearStart(AddYears(data_ini,-3)))<=$(=YearEnd(AddYears(data_fin,-3)))">} W_IMPONIBILE_RIGA_FATT_ACQ_IMB)

/

SUM({<DATA_FATT_FORN_FATT_ACQ_IMB={">=$(=YearStart(AddYears(data_ini,-3)))<=$(=YearEnd(AddYears(data_fin,-3)))">} W_QTA_UM_FATT_ACQ_IMB)

,

// se dopo tre anni non ho acquisti non ha più valore

0

)

////// -3 fine

)

)

)

[Aasir]

If you need to group the data by KEYARTMERCBUD after multiplying each C_ARTICLE_CONS with its cost, you can adjust the expression accordingly.
Aggr(
Sum(
if(
DATA_MOV_CONS >= data_ini and DATA_MOV_CONS <= data_fin and C_CATEGORIA_ARTICOLO_CONS <> 'SLP' AND C_CATEGORIA_ARTICOLO_CONS <> 'PFI',
W_QTA_MOV_CONS * $(v_pm_periodo_imb),
0
)
),
KEYARTMERCBUD, C_ARTICOLO_CONS
)

[paolojolly]

I tried your solution and I used as dimension the following script

=Aggr(
sum(
Aggr
(
sum(
{<DATA_MOV_CONS={">=$(=data_ini)<=$(=data_fin)"},C_CATEGORIA_ARTICOLO_CONS-={SLP,PFI}>}
W_QTA_MOV_CONS

)

*
$(v_pm_periodo_imb_vinc)

,
KEYARTMERCBUD, C_ARTICOLO_RIF_OP_PF, C_ARTICOLO_CONS
)
)
, KEYARTMERCBUD)

but I get zero as result in pivot table. I think there’s something wrong with the expression

{<DATA_MOV_CONS={">=$(=data_ini)<=$(=data_fin)"},C_CATEGORIA_ARTICOLO_CONS-={SLP,PFI}>}

I tried to simplify everything with the following expression.

=Aggr(

sum(

Aggr

(

sum(

if(

DATA_MOV_CONS >= $(data_ini) and DATA_MOV_CONS <= $(data_fin) and C_CATEGORIA_ARTICOLO_CONS <> 'SLP' AND C_CATEGORIA_ARTICOLO_CONS <> 'PFI',

W_QTA_MOV_CONS

, 0)

)

*

(

SUM(

IF(DATA_FATT_FORN_FATT_ACQ_IMB >= $(data_ini) and DATA_FATT_FORN_FATT_ACQ_IMB <= $(data_fin),

W_IMPONIBILE_RIGA_FATT_ACQ_IMB

)

)

/

SUM(

IF(DATA_FATT_FORN_FATT_ACQ_IMB >= $(data_ini) and DATA_FATT_FORN_FATT_ACQ_IMB <= $(data_fin),

W_QTA_UM_FATT_ACQ_IMB

)

)

)

,

KEYARTMERCBUD, C_ARTICOLO_RIF_OP_PF, C_ARTICOLO_CONS

)

)

, 

KEYARTMERCBUD

)

it works if I only have one item C_ARTICOLO_RIF_OP_PF selected, but it goes wrong if I remove filters from the C_ARTICOLO_RIF_OP_PF field

[paolojolly]

the variable $(v_pm_periodo_imb) has a sum inside and I have to place it as below

=Aggr(
Sum(
if(
DATA_MOV_CONS >= data_ini and DATA_MOV_CONS <= data_fin and C_CATEGORIA_ARTICOLO_CONS <> 'SLP' AND C_CATEGORIA_ARTICOLO_CONS <> 'PFI',
W_QTA_MOV_CONS ,
0
)
)*
$(v_pm_periodo_imb)

,
KEYARTMERCBUD, C_ARTICOLO_CONS
)

but this solution groups the data as follows

with the following expression

aggr(
sum(

Aggr(
Sum(
if(
DATA_MOV_CONS >= data_ini and DATA_MOV_CONS <= data_fin and C_CATEGORIA_ARTICOLO_CONS <> 'SLP' AND C_CATEGORIA_ARTICOLO_CONS <> 'PFI',
W_QTA_MOV_CONS ,
0
)
)*
$(v_pm_periodo_imb)

,
KEYARTMERCBUD, C_ARTICOLO_CONS
)
)
, KEYARTMERCBUD)

I get the desired result with

aggr(
sum(

Aggr(
Sum(
if(
DATA_MOV_CONS >= data_ini and DATA_MOV_CONS <= data_fin and C_CATEGORIA_ARTICOLO_CONS <> 'SLP' AND C_CATEGORIA_ARTICOLO_CONS <> 'PFI',
W_QTA_MOV_CONS ,
0
)
)*
$(v_pm_periodo_imb)

,
KEYARTMERCBUD, C_ARTICOLO_CONS
)
)
, KEYARTMERCBUD)

it works if I only have one item C_ARTICOLO_RIF_OP_PF selected, but it goes wrong if I remove filters from the C_ARTICOLO_RIF_OP_PF field