Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set analysis

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

18 Replies
Anonymous
Not applicable
Author

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

Not applicable
Author

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
Not applicable
Author

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

Not applicable
Author

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
Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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
Not applicable
Author

Hi,

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

Do you still have a performance issue?

Regards,

Janusz

Not applicable
Author

Hi,

Yes i did it.

Nothing changed.

regards

Giampiero