topic Problem in reusing expressions in QlikView

Problem in reusing expressions

Tue, 28 Feb 2012 09:24:14 GMT

Hi,

In order to re-use expression (so i can use it in different charts without typing the expression again), i use LET in the script, and then the $() in the chart.

Nevertheless, when there is a string comparison in the expression (see below the PricingModel=CPC) the expression is not valid.

LET vRevenue = 'Sum(if(PricingModel=CPC,cost))';

how can i cause this expression to be valid?

Just to clear it up, when i use the expression Sum(if(PricingModel='CPC',cost)) directly in the chart it is working fine.

thanks,

Guy

Problem in reusing expressions

CELAMBARASAN — Tue, 28 Feb 2012 09:33:00 GMT

Hi,

You approach is correct.but let is like assigning value.

vRevenue has the value Sum(if(PricingModel=CPC,cost)) here CPC is as string so it should be enclosed in quotes in your case it takes as a variable or fieldname.

Use Set Instead of Let

Check with this

SET vRevenue = Sum(if(PricingModel='CPC',cost));

Now use $(vRevenue).

Celambarasan

Problem in reusing expressions

Tue, 28 Feb 2012 09:36:55 GMT

Hi Guy,

I use the variables for such re-usage of expressions. Go to Settings/Variable Overview then click the add button to add a new variable with name as vRevenue, click the newly added variable and set the definition as =Sum(if(PricingModel=CPC,cost)) then use this variabe in expressions as =$(vRevenue).

Best Regards,

Sajeevan

Problem in reusing expressions

Tue, 28 Feb 2012 09:56:25 GMT

Thanks, It works! The idea is indeed using SET instead of LET when defining the expression in the script.

Problem in reusing expressions

Tue, 28 Feb 2012 10:04:49 GMT

Hi Sajeevan,

That will not work, because if i use the expression in the Varaible Overview then it is evaluated immediatly,

so the value remains the same for all rows in the chart (when using ithis expression in a chart.)

Thanks anyway,

Guy

Problem in reusing expressions

CELAMBARASAN — Tue, 28 Feb 2012 10:14:51 GMT

Hi,

You can use the approach suggested by Sajeevan Govindan but without the equal sign.

Defining there with '=' sign is like Let without equal sign is like Set.

like this Sum(if(PricingModel='CPC',cost))

Celambarasan

Problem in reusing expressions

Tue, 28 Feb 2012 10:31:34 GMT

I never had issues even if I use "=" sign. I use it regularly. Some time I load all my expression from an excel file and use it assigning to a variable.

Re: Problem in reusing expressions

CELAMBARASAN — Tue, 28 Feb 2012 10:36:58 GMT

Hi,

If you use = sign then its like predefined calculation like storing the calculated value to the variable not expression.

Check by this approach

Have year and month dimension in chart.

Store vSales variable value as =Sum(Sales).

Check by adding Expression to the chart as $(vSales) it will give same value for all dimension values.

Celambarasan

Problem in reusing expressions

jagan — Tue, 28 Feb 2012 10:43:28 GMT

Hi Sanjeevan,

If you assign '=' for an expression it precalculates, it is like static calculation, all the rows in chart has same value.

If you didn't use '=' for an expression it is dynamic, it calculates dynamically in the chart, all the rows in the chart will have corresponding value.

This is the difference between the two.

Regards,

Jagan.

Problem in reusing expressions

Tue, 28 Feb 2012 11:01:23 GMT

Hi Guy, Celambarasan and Jagan,

I forgot to mention that the expression should be in single quote.

='sum(Sales)'

='Sum({<PricingModel={"CPC"}>} cost)'

Regards,

Sajeevan

Problem in reusing expressions

CELAMBARASAN — Tue, 28 Feb 2012 11:15:24 GMT

Hi Sajeevan,

Yes now it will work.Its like assigning a string value to a variable.

Celambarasan

Problem in reusing expressions

Tue, 28 Feb 2012 12:43:21 GMT

Hו Sajeevan Govindan

I think the expression ='Sum({<PricingModel={"CPC"}>} cost)' works becuase you use SET ANALYSIS in the expression, otherwise is not.

Thanks a lot for all opf you!

Problem in reusing expressions

Tue, 28 Feb 2012 12:53:27 GMT

Yes, you are right.