$ Expansion inside $Expansion

[#1]

Hi Folks

I wonder if anybody can help with this.

I have a variable called vCompDate which is set as follows:

SET vCompDate = "=GetFieldSelections(DT_ComparisonDate)";

When I use this inside an expression in a chart (for example IF DT_RepDate = $(vCompDate) .........) then it evaluates the dollar expansion and the expression becomes IF DT_RepDate = "31-Dec-08" .......... which is exactly what I need.

Now, I want to use this dollar expansion inside another variable which is going to be used as a dollar expansion, so I set a variable like this:

SET vComplexValue = "=Sum(if(DT_RepDate=$(vCompDate),Cost,Price))"

However, my problem now is that when I add =$(vComplexValue) into a chart, the expression is evaluated as follows:

=Sum(if(DT_RepDate="=GetFieldSelections(DT_ComparisonDate)",Cost,Price))

This expression doesn't work, because the embedded dollar expansion is not being evaluated.

Any thoughts on this, I'm really banging my head against a brick wall here.....

[#2]

Hi,

Could you change vCompdate like that ;

SET vCompDate = "GetFieldSelections(DT_ComparisonDate)";

Best Regards,

Önder

[#3]

Thanks for the response Onder, unfortunately this doesn't work, it doesn't evaluate the $expansion once it's put into the chart as an expression.

[#4]

I tried the following script

SET vCompDateExp = "GetFieldSelections(DT_ComparisonDate)";

SET vCompDate = "=$(vCompDateExp)";

SET vComplexValueA = "=Sum(if(DT_RepDate=$(vCompDate),Cost,Price))";

SET vComplexValueB = "=Sum(if(DT_RepDate=$(vCompDateExp),Cost,Price))";

and I get the following output:

vComplexValueB "=Sum(if(DT_RepDate=GetFieldSelections(DT_ComparisonDate),Cost,Price))"

vComplexValueA "=Sum(if(DT_RepDate==GetFieldSelections(DT_ComparisonDate),Cost,Price))"

vCompDateExp "GetFieldSelections(DT_ComparisonDate)"

vCompDate "=GetFieldSelections(DT_ComparisonDate)"

vComplexValueA expands with 2 equal symbols but without the quotes you're seeing. I think that if you need to include the equal symbol you must use 2 different variables. Using the first one to build the second. Does it help?

[#5]

Hello,

I would also suggest to remove the leading = in the variables:

SET v1 = "Now()";
SET v2 = "date($(v1),'DD.MM.YYYY')";

The expression (in a text box) =$(v2) calculates correctly.

I have not tested it, but if the = is needed, something like $(=$(v2)) could work ...

hth,
Thilo

[#6 · Accepted solution]

Hi Everyone

Thanks to all of you who replied to this, I have used most of your suggestions and come up with a solution that works, the key bit of information was the replacing of $ symbols with chr(36) as follows:

SET vCompDate = "=GetFieldSelections(DT_ComparisonDate)";
LET vPriorInvCap = 'Sum({$<DT_ReportDate={"' & chr(36) & '(vCompDate)"},DT_ComparisonDate={"*"}>}_rCost+_rUnfunded+_rDeferred)+(Sum({$<DT_ReportDate={"' & chr(36) & '(vCompDate)"},DT_ComparisonDate={"*"}>}_rUnfunded_PShop+_rDeferred_PShop)*Max({$<DT_ReportDate={$(vCompDate)},DT_ComparisonDate={"*"}>}FX_toUSD_Inv_RepDate))';

You'll notice though, that I'm not replacing all of the $ symbols (a mistake I made when first trying this), only the $ expansion symbols are replaced, the above solution works really well and certainly makes life very much easier when modifying and using these complex expressions.

Thanks to everyone for getting involved in this.

Regards,