IF logic statement causing memory issues

[#1]

Hi,

I have a Straight Table and I want to restrict the data displayed based on the following logic within a Calculated Dimension (so that I can suppress when value is null):

=IF(OnTrackingSheet='Yes' OR Over500k?='Yes', ID,

IF(Progress<>'Closed' AND RAG='Red' AND Active='Active', ID,

NULL()))

This causes the error message box:

"Out of virtual and / or logical memory allocating 2MB"

The Straight Table then breaks displaying error:

"Allocated memory exceeded"

My question is how do I retain this logic in a more efficient manner that doesn't kill the object?

The data required for the above IF statement is spread across four tables. Based on a process of elimination the Over500k?='Yes' component seems to be the trouble-maker. Once Over500k?='Yes' is removed everything works ok. This, needless to say, is not acceptable from a user perspective.

As they are OR statements I cannot use selection triggers.

I am open to creating this logic in the script at load time however I have never linked up multiple tables like this before....is this even feasible?

Any help greatly appreciated.

Thanks!

[Gysbert_Wassenaar]

Try [Over500k?] instead of Over500k?. If that doesn't work try renaming the field in the script to for example IsOver500k.

[#3]

Thanks for the suggestion Gysbert.

I have renamed to Over500k and [Over500k] however the issue remains.

[Gysbert_Wassenaar]

Then the calculated dimension is probably just too complicated to calculate for your data set. Maybe you can change your expressions over to set analysis expressions:

sum({<OnTrackingSheet={'Yes'},Over500k={'Yes'},Progress-={'Closed'},RAG={'Red'},Active={'Active'}>} MyValue)

The expression editor will flag -= as wrong syntax, but that's a bug in the expression editor.

Also, I'd replace 'Yes' and 'No' values in the script with dual values so they have a numeric value as well. Calculations on numeric values are faster than string comparisons. See this blog post for a quick fix.

[er_mohit]

Try this

Aggr(Only({$<OnTrackingSheet={'Yes'}+Over500k?={'Yes'},Progress-={'Closed'}>}*RAG={'Red'}*Active={'Active'} ID), ID)