Creating a formula as a variable

[#1]

Hi,

I have created a variable for my formula that calculates total Sales among the format settings (see the last line). My goal is to be able to enter the formula of Sum(vSalesFormula) into an expression in any report and for "if(Sales>1,Sales)) + sum(Sales2)" to be inserted where vSalesFormula is and it calculate.

This way as I add more reports I can adjust all reports at the same time by adjusting the formula below instead of going into each expression in each report.

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='D/MM/YYYY';
SET TimestampFormat='D/MM/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET vSalesFormula = '(if(Sales>1,Sales)) + sum(Sales2))';

This isn't working.... can anyone see a way to fix this?

Thanks

David

[biester · Accepted solution]

Hi,

try to use $(#vSalesFormula) in your expression (numerical evaluation of a variable).

Rgds,
Joachim

[eric_dielessen]

• First of all the number of brackets seem to be incorrect; I count 3 starting brackets and 4 ending brackets. The last one should be removed.
• Secondly, try to remove the quotes around the formula.
• Then still it will only work when Sales is unique for each dimension in the chart; otherwise you probably need to use If(Sum(Sales)>1, Sum(Sales)) + Sum(Sales2)

[#4]

Thankyou both, I used a little from each reply to make it work.

This is much more efficient

David