Universe design help with formula

[Former Member]

Hi experts,

I'm relatively new to BO, and I have a question regarding one formula in universe design.

So I have a table called Sales_agent_position which contains Valid_From and Valid_To dimensions.

What I need to get is Current_Position_Flag(Y/N) based on Valid_To dimension. So if Valid_To dimension isNULL it means it is a current position and my Flag should be 'Y' else 'N'.

So i created a object called Current_Position_Flag(Y/N) and entered following formula:

CASE WHEN sales_agent_position.valid_to isNULL THEN (CUG\Current Position Flag(Y/N)) ='Y'

ELSE

(CUG\Current Position Flag(Y/N))= 'N'

END

And I tried few differen variations of the formula, and I can't get it right, syntax error every time. Can you please help me?

[jeff_crisp · Accepted solution]

I am not an expert, but I believe there are 2 ways to accomplish what you are trying to do:

1)  You may want to insert a calculated column on your table at the data foundation layer.  If you give that calculated column the formula that you have listed and the name "Current_Position_Flag", then you can create a dimension with that column.

2) Try adjusting your CASE statement for this object to the following:

CASE WHEN sales_agent_position.valid_to IS NULL

     THEN 'Y'

     ELSE 'N'

END

[Former Member · Accepted solution]

Thanks, second option works like a charm.