Filter expression in Calculation View

[Former Member]

Hi,

I am creating a filter in the calculation view based on an input parameter. User give the value of the input parameter as the begin date and I filter the data of the last 12 month from the begin date. The filter looks like follows: date <= $$begin_date$$ and data > $$begin_date$$ - 100 (date is integer in form 201509)

However I found the filter doesn't work if I put any mathematical operator inside (e.g. the minus - ). I tried created a calculated column end_date = $$begin_date$$ - 100 at first and set the filter as date <= $$begin_date$$ and data > end_date. It works but the performance is very low, much slower than I set filter as e.g. date <= $$begin_date$$ and data > 201410.

I cannot find the correct syntax to put the mathematical operator in the filter expression, do you know any secret rule to write such expression?

I considered a second parameter for the end_date or wrapper the calculation view again in sqlscript calculation view to implement the filter. But is there any better way to do that?

Thanks and regards,

Hai

[christianschuer]

Hi Hai,

Referring to this blog , it is not possible to put mathematical operators into the filter expression, due to the fact that "Projection expression does not provide flexibility for data manipulation".

Using calculated columns influences the performance of the view. Hence this model is not recommended.

As you mentioned, the two possible solutions are using a second parameter or deriving the input parameter within a SQL Script based View.

Kind regards,

Christian