Complete noob here in comparison to most users in these forums
I have a variable which runs the following formula
=+NoFilter( Where (=200907)*)
Basically takes the total budget number for all data and adding on the budget number for just this month again, multiplied by a ratio set in another variable.
This works fine.
Problem is I want to swap out the 200907 for a Prompt the user enters when refreshing. Basically asking for the year/month combination as above. But when I turn the formula to this…
=+NoFilter( Where (=)*)…I get the age old syntax error.
I’ve checked the Prompt Financial Period variable and it is a number and in the correct format.
Your issue is coming about because the “Where” clause must have a constant, it cannot compare to another variable. It’s covered in the Reporter FAQ in this post but to give you a hint…
You will need to create several variables. The first is your prompt “collector” which you already have; it captures the value in the prompt. Next, create a flag variable that looks something like this:
=if (<Financial Period >=<Prompt Financial Period>) Then 1 Else 0
Call this variable something like Period Flag.
Then your final variable looks like this:
=<Budget No>+NoFilter(<Budget No> Where (<Period Flag>=1)*<MTD Ratio>)
By creating the extra variables, you will satisfy the requirement that the where clause is compared to a constant. Make sense?
(BOB member since 2002-06-06)