Expression behaving differently when loaded as a variable

[#1]

I have the following expression, which I was using in a few places:

sum({<YearMonthField={'<=$(=max(YearMonthField))>=$(=max(YearMonthField) -300)'}>}Plays)

I started moving some common expressions into a CSV file and importing them in script as variables, so that I could keep everything in one place.  When I did this, the above expression started giving a different result.

It's being imported in my file as:

"var4a","sum({<YearMonthField={'<=$(=max(YearMonthField))>=$(=max(YearMonthField) -300)'}>}Plays)"

To debug, I created a straight table where I put 2 columns side by side - one is the literal expression, the other is just $(var4a).

I attached a picture of what I'm seeing.  It's like the "=max(YearMonthField)" parts of the expression are not being evaluated when it's loaded as a variable.

Any ideas? Thanks!

[sudeepkm]

EDIT:

sum({<YearMonthField={"<=$(=max(YearMonthField))>=$(=max(YearMonthField) -300)"}>}Plays)

pls use "$(=max(YearMonthField))" inside set analysis

[#3]

So it's just double quote instead of single quote?

How would I load this in my CSV file of expressions?  I tried a \" to escape the quote in the expression, but it seems to literally load \" instead of just ".

[#4]

I changed my input file from CSV to XLS so that I could more easily embed the double quotes in the expression values. I reloaded everything, and I am still seeing the same issue, so this does not seem to have helped.

[#5]

hey I'm not sure if this will resolve your issue but I had the same end result where a piece of the expression within a dollar expansion wasn't being evaluated when passed using a variable and found a solution so am just passing it on in case of use.

Background of my issue - to improve transparency and maintenance, I defined my expressions in the script with variables, which I then used in all my charts and tables etc.

When I had my expressions defined directly in the chart/table, they worked grand, but when I moved those exact same definitions into variables some didn't work because a dollar expansion wasn't getting evaluated correctly.

For example I had the following set analysis expression:

sum({1<

      TDate={'$(vSelTDate)'}

      ,IsTDay={'Y'}

      ,Dept={'HR'}

>}[Total Qty])

I converted this to a variable as follows (note that I used double quotes around the set analysis values and the whole variable definition is between 2 single quotes):

LET vTotalQtySelDate =

  'sum({1<

      TDate={"$(vSelTDate)"}

      ,IsTDay={"Y"}

      ,Dept={'HR'}

  >}[Total Qty])';

However the TDate filter in this would not evaluate correctly - like your example, it came out as

TDate={""}

So after a search on this forum, I found a fix/workaround that has resolved this for me; when the expression of your variable contains a dollar expansion, you have to separate out the $ symbol into its own string.

So with the above example, the following definition works perfectly.

As this can be hard to read in a browser, I'll just point out that TDate={ is followed by a double quote which is the start of the value to be passed in to filter the TDate field and then a single quote to end the string.

LET vTotalQtySelDate =

  'sum({1<

       TDate={"' & '$' & '(vSelTDate)"}

      ,IsTDay={"Y"}

      ,Dept={'HR'}

  >}[Total Qty])';

hope it helps anyways!

[jaimeaguilar]

Hi,

you can also use functions Chr(34) for double quotes or chr(39) for single quotes as escape functions,

regards