topic Improving expression for Performance reasons in QlikView

Improving expression for Performance reasons

Wed, 22 Jun 2011 12:40:02 GMT

Hi Guys,

I have a query I am hoping someone can help me out with.

I have a chart in an application that has multiple expressions for different measures. I have about 7 expressions and the content of the expressions is pretty similar to the script below

Sum({<STAT = {'CNS'}, LAST_DATE={'Y'},

PRODUCT = {'C', 'L'},

AREA ={'Usa', 'China', 'England', 'Wales', 'Italy'}

>} VALUE)

The problem I have is that the chart is really slow because of the excess use of set analysis and multiple expressions.

Can someone please kindly suggest a way for me to do something with the expressions or possibly move some logic into the script to improve the performance of this chart?

Thanks

Improving expression for Performance reasons

brenner_martina — Wed, 22 Jun 2011 13:01:20 GMT

Hi,

if your selections are static try to use if-then-else:

Sum(

If(STAT = 'CNS' And

LAST_DATE='Y' And

Match(PRODUCT , 'C', 'L') And

Match(AREA ,'Usa', 'China', 'England', 'Wales', 'Italy'),

VALUE)

Greetings from Munich

Improving expression for Performance reasons

Wed, 22 Jun 2011 13:28:40 GMT

Thanks Brenner.

Unfortunately, the selections are not static and so for one expression, I could have for Area (Japan, Turkey, Mexico) and for another expression I could have (Brazil, Scotland, Canada), so the sum if statement does not work as expected.

Improving expression for Performance reasons

brenner_martina — Wed, 22 Jun 2011 13:59:47 GMT

Hi, zebadguy,

if only AREA is changing by selection you can combine like this:

Sum({<AREA ={$(varArea)}>}

If(STAT = 'CNS' And

LAST_DATE='Y' And

Match(PRODUCT , 'C', 'L'),

VALUE)

and the variable varArea is defined with the function concat() for the actual selection., like:

=Chr(39)&Concat(AREA,Chr(39)&','&Chr(39))&Chr(39)

Chr(39) = Apostroph

Greetings from Martina from Munich