Set analysis

[#1]

Hi everybody,

I have some problem with an expression.

At the moment I have this espression (in my pivot table) that works perfectly.

if(GetFieldSelections(Text) = 'Escludi'
,//THEN
sum({$<D_LIV1={ECO},Partite.Anno={ $(vvMaxYear) },Partite.FCONTO= {"<>Partite.FCONTO like 'G*'"}>} Partite.WRBTR)
,//ELSE
sum({ $<D_LIV1={ECO},Partite.Anno={ $(vvMaxYear) }> } Partite.WRBTR)
)

Unfortunately, sometimes I have some problem with the memory of my object (pivot table).

I know that IF statement is not so good for performance so, I'm trying to put my IF in the set analysis.

I've just tried this way:

sum({$<D_LIV1={ECO},Partite.Anno={ $(vvMaxYear) }, Partite.FCONTO ={ $( =if(GetFieldSelections(Text) = 'Escludi', Partite.FCONTO= {"<>Partite.FCONTO like 'G*'"} ) ) } >} Partite.WRBTR)

No error are mensioned but it doesn't show what I'm expecting to have (the result is ZERO on every row of my table).

Any suggestion to improve my expression ?

Am I well imported the IF in the set analysis ?

Thanks in advance

[Anonymous]

Hi,

I think there is an error in the 'THEN' sum expression.

Try this:

if(GetFieldSelections(Text) = 'Escludi'
,//THEN
sum({$<D_LIV1={ECO},Partite.Anno={ $(vvMaxYear) },Partite.FCONTO= {"*"}-{"G*"}>} Partite.WRBTR)
,//ELSE
sum({ $<D_LIV1={ECO},Partite.Anno={ $(vvMaxYear) }> } Partite.WRBTR)
)

Cheers!

Janusz

[#3]

Hi Janusz,

thanks.

At the moment this expression

if(GetFieldSelections(Text) = 'Escludi'
,//THEN
sum({$<D_LIV1={ECO},Partite.Anno={ $(vvMaxYear) },Partite.FCONTO= {"<>Partite.FCONTO like 'G*'"}>} Partite.WRBTR)
,//ELSE
sum({ $<D_LIV1={ECO},Partite.Anno={ $(vvMaxYear) }> } Partite.WRBTR)
)

works perfectly.

What I'm to do is change that expression in this one:

sum({$<D_LIV1={ECO},Partite.Anno={ $(vvMaxYear) }, Partite.FCONTO ={ $( =if(GetFieldSelections(Text) = 'Escludi', Partite.FCONTO= {"<>Partite.FCONTO like 'G*'"} ) ) } >} Partite.WRBTR)

Unfortunately it doesn't work.

I'm trying to understand why.

Thanks a lot

[Anonymous]

Hi,

I'm not sure why you want to do it this way, but this is your call.

Try this:

sum({$<D_LIV1={ECO},Partite.Anno={ $(vvMaxYear) }, Partite.FCONTO ={ $( $(=if(GetFieldSelections(Text) = 'Escludi', 'Partite.FCONTO= {"<>Partite.FCONTO like 'G*'"}'))) } >} Partite.WRBTR)

Regards,

Janusz

[#5]

Hi,

unfortunately it doesn't work. The result is still zero.

About the other expression.

Your way:

if(GetFieldSelections(Text) = 'Escludi'
,//THEN
sum({$<D_LIV1={ECO},Partite.Anno={ $(vvMaxYear) },Partite.FCONTO= {"*"}-{"G*"}>} Partite.WRBTR)
,//ELSE
sum({ $<D_LIV1={ECO},Partite.Anno={ $(vvMaxYear) }> } Partite.WRBTR)
)

it's very interesting. THANKS.

I'm trying to change this way to write the expression because I have some performance problem.

I know that eliminating if statement you can improve the performance.

THANK YOU very much for your help.

I'm really appreciating.

[Anonymous]

Hi,

By removing the if statement from the aggregation expression gives you performance improvement.

In your case it will change nothing. There must be something else.

First, I would check the data model to make sure that it has been created according to the 'best practices'. You can search community for more info.

Regards,

Janusz

[#7]

I see.

Thank you Janusz.

My thread is been created because of my performance problem.

That expression is on a pivot table that often give me the message "out of memory".

I was trying to resolve this problem.

I have only three tables on my data model and the biggest table have 3.6 millions of records.

Thank you so much for your kindness.

best regards

Giampiero

[#8]

Hi Janusz,

I made a simple test to understand my performance problem.

As you remember I have this expression:

if(GetFieldSelections(Text) = 'Escludi'
,//THEN
sum({$<D_LIV1={ECO},Partite.Anno={ $(vvMaxYear) },Partite.FCONTO= {"<>Partite.FCONTO like 'G*'"}>} Partite.WRBTR)
,//ELSE
sum({ $<D_LIV1={ECO},Partite.Anno={ $(vvMaxYear) }> } Partite.WRBTR)
)

It works perfectly but I have serious performance problem.

I thought the problem was my if statement, so i tried to eliminate the IF and i was trying this expression:

sum({$<D_LIV1={ECO},Partite.Anno={ $(vvMaxYear) }, Partite.FCONTO ={ $( $(=if(GetFieldSelections(Text) = 'Escludi', 'Partite.FCONTO= {"<>Partite.FCONTO like 'G*'"}'))) } >} Partite.WRBTR)

Unfortunately it doesn't work.

Just to be sure it's the IF statement the problem, yesterday I chenged my expression writing only:

sum({ $<D_LIV1={ECO},Partite.Anno={ $(vvMaxYear) }> } Partite.WRBTR)

Now I don't have any more performance problem.

I can't use this expression and I have to make right this one

sum({$<D_LIV1={ECO},Partite.Anno={ $(vvMaxYear) }, Partite.FCONTO ={ $( $(=if(GetFieldSelections(Text) = 'Escludi', 'Partite.FCONTO= {"<>Partite.FCONTO like 'G*'"}'))) } >} Partite.WRBTR)

Have you some suggestion to help me ?

Thank you so much

Giampiero

[Anonymous]

Hi,

Did you try to replace your expression with the one from my first reply?

Do you still have a performance issue?

Regards,

Janusz

[#10]

Hi,

Yes i did it.

Nothing changed.

regards

Giampiero