Problem in reusing expressions

[#1]

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

[CELAMBARASAN · Accepted solution]

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

[#3]

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

[#4]

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

[#5]

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

[CELAMBARASAN]

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

[#7]

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.

[CELAMBARASAN]

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

[jagan]

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.

[#10]

Hi Guy, Celambarasan and Jagan,

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

like

='sum(Sales)'

or

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

Regards,

Sajeevan