Variables bug with Expression editor?

[#1]

I defined a small number of variables based on table values in a script so that I could refer to the variable, rather than directly to the table value using an inter-record function in a chart formula. This worked fine in a small application. 

However, when I scaled it up to an app with a lot more data, but similar structure, it appears to cause problems.  In particular, the expression editor shows "Expression OK", but the expression produces null values.  Furthermore, if you type random stuff at the end of the expression, the expression editor continues to show "Expression OK", instead of "Garbage after ')'...", as one might expect it to do.

Any suggestions?

Thanks!

[Peter_Cammaert]

And I can imagine that you made sure a hundred times before that the name in both the variable overview and the set expression is the same, so I won't nag you about the obvious.

What exactly is the value of variable Ta1 at the time of expression failure? Or to put it differently:

What does the expression look like after you replace $Ta1 with its actual value ($-substitution debug trick No 1)? Is the expression editor showing any wiggly lines?

[#3]

No wiggly lines.  If I replace $(Ta1) with its value - something like 'Tariff 1' - the expression works.  With $(Ta1), the expression returns the correct value.

[#4]

Hello again all,

Sorry for the delay in getting back to you about this.  I have experimented a bit with the big and little versions of the model.  Firstly, I tried scaling down the data inputs for the big model by aggregating (summing transactions over the month so that I load just the monthly total and eliminating unneeded fields etc.) this takes the number of rows loaded from 5.5m to a few hundred thousand.  I also got rid of all irrelevant charts etc. to reduce memory load.  This didn't make any difference to the behaviour of the expression - i.e. using the variables Ta1, Ta2... gives a null result.  Substituting the string values of the variable does work, but leads to rather verbose code and the chart object runs out of memory unless I select a subset of the data.

So I created more variables to see if replacing larger parts of the expression formulae with variable, leading to simpler code and, perhaps, avoiding any possible issue with combining the variables with set theory structures might help.  This removes the object memory issue, but despite the variables showing correct values in the Variable Overview, they produce nulls (or zeros in the context of my formula). So I created a test table (see below) to test which variables might be causing the problem (the expression value in each case is just $(varname).

As you can see, most, but not all of the variables produce a null.

Doing the same with the little model led to the following test table:

.

Apart from the T/Ta difference in naming, the tables are the same, as are the scripts that produce the variables.

Any thoughts?

[Peter_Cammaert]

So the issue is not with the T/Ta variables, but with the others (O1-O4, R1-R4)?

Any chance of posting the cut-down version of the document that is experiencing problems?

[#6]

Well, in the little model, the T variables work in the original table ("Table: by user"), for example with:

Sum({$<[Tariff Name]= {$(T1)}>} [Rental per month])

but not in the test table ("Variables") with $(T1).

Little model attached.

[#7]

OK, I think I have found the cause of the problem and a solution.  I changed to inputting the tariff table using an INLINE statement, transferring the existing values to the table using the Inline wizard.  I noticed that the tariffs for the big model included a currency symbol, which it seems is not understood as such when the value is read into a variable.  Get rid of the currency symbol and the variable values are read correctly.  The values that actually are meant to be strings don't show up properly when you set the expression value to $(variablename), but they do seem to work when you apply them as part of a set theory expression, or a SUM( IF(... expression.  They work with the $(variablename) alone structure if you edit them in Variable Overview to enclose them in single quote marks (the Inline wizard encloses them in double quote marks).

Thanks,

Leo

[Peter_Cammaert]

Ok, but why then the difference between the small and the big model? Different data?

[#9]

Yes, the two models both read their data from Excel different spreadsheets. The big model's tariffs spreadsheet was formatted with some of the fields as currency.  This does not seem to cause a problem if you refer to the field in the resulting table directly in an expression, but it does if you transfer it to a variable.

Now I need to figure out how to count the number of times each tariff is the cheapest - I'll post another query if, as seems likely at the moment, I don't succeed!

In the meantime, thanks everyone for all your help on this one.