topic Creating a formula as a variable in QlikView

Creating a formula as a variable

Tue, 18 Aug 2009 12:18:18 GMT

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

AW:Creating a formula as a variable

biester — Tue, 18 Aug 2009 16:18:41 GMT

Hi,

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

Rgds,
Joachim

Creating a formula as a variable

eric_dielessen — Tue, 18 Aug 2009 17:57:15 GMT

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)

Creating a formula as a variable

Wed, 19 Aug 2009 12:59:58 GMT

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

This is much more efficient

David