Correct definition of variables

[burgersurfer]

Hi

I have a calculation that occurs numerous times through my document, and it looks like the client is going to have ongoing changes on this calculation. So the easiest way to handle it, will be to create a variable with the formula, and only refer to this variable in the objects. But, I cannot get this variable defined correctly. Some help please?

The formula is:

=sum({$<FinYear = {'$(vPY0)'}, TD_TYPE ={'0'} >} TD_LINE_VALUE_EXCLUSIVE) - sum({$<FinYear = {'$(vPY0)'}, TD_TYPE ={'1'} >} TD_LINE_VALUE_EXCLUSIVE)

With vPY0 already a variable indicating the current selected year. I wanted to (ideally) create 1 variable for this calc, but it might make sense to define 2 as follows:

vSales as sum({$<FinYear = {'$(vPY0)'}, TD_TYPE ={'0'} >} TD_LINE_VALUE_EXCLUSIVE)

and

vReturns as sum({$<FinYear = {'$(vPY0)'}, TD_TYPE ={'1'} >} TD_LINE_VALUE_EXCLUSIVE)

Though my results are incorrect - I get the total sales across the group for every line, instead of per location as when I use the initial Calc, in the object I then use the expression vSales - vReturns, also tried Sum(vSales) - Sum(vReturns)

What to fix please?

[rwunderlich]

Don't include the "=" in the variable definition.

-Rob

[#3]

Use $(=variablename) as the syntax in set analysis, e.g.,

sum({$<FinYear = {'$(=vPY0)'}, TD_TYPE ={'1'} >} TD_LINE_VALUE_EXCLUSIVE)

[burgersurfer]

Thanks both, but I am now more confused:

Removing the = sign in the variable gives not result

Combining vhuynh's code with a = sign in from of the variable get me somewhere, but not the result. Basically this puts me back at getting the column's total figures in every cell, instead of getting the per-store result with the total as a sum

So I now have this:

vSales=sum({$<FinYear = {'$(=vPY0)'}, TD_TYPE ={'0', '34'} >} TD_LINE_VALUE_EXCLUSIVE)

vReturns=sum({$<FinYear = {'$(=vPY0)'}, TD_TYPE ={'1', '35'} >} TD_LINE_VALUE_EXCLUSIVE)

(the '34' and '35' that's now added is the reason for using a variable)

Then in the expression:

=vSales - vReturns

[Oleg_Troyansky]

In the expression, use

=$(vSales) - $(vReturns)

[burgersurfer]

Great! Thanks

So the answer is to omit the = sign in the variable and put the variable in $( ) notation in the expressions. Thanks all !!!