Initialize a dimension using a formula

[former_member681376]

Dear all,

I'd like to initialize the dimension Variant (VA) in the columns of a report using a formula.

Depending on the period selected (1, 2, 3, ...) the dimension variant should be automatically populated (BKUP01, BKUP02, BKUP03,...).

I don't know what would be the correct syntax.

I'm trying this one below but there's a syntax error (see att.)

IF(MONTH_OF (REF_PER) = 1; {VA = BKUP01}

IF(MONTH_OF (REF_PER) = 2; {VA = BKUP02}

IF(MONTH_OF (REF_PER) = 3; {VA = BKUP03}

...

If someone could help me, I'd be grateful.

Kind regards

[former_member681376]

Hi,

Actually, I need to verify another condition in the same formula.

I need to check if VA = BKUP01; VA = BKUP02, VA = BKUP03, ... exists. If not, I should use VA=2.

I tried the following but it gives me a syntax error:

IF (MONTH_OF (REF_PER) = 1 ; {VA = BKUP01} ; IF (MONTH_OF (REF_PER) = 2 ; {VA = BKUP02} ; IF (MONTH_OF (REF_PER) = 3 ; {VA = BKUP03} ; IF (MONTH_OF (REF_PER) = 4 ; {VA = BKUP04} ; IF (MONTH_OF (REF_PER) = 5 ; {VA = BKUP05} ; IF (MONTH_OF (REF_PER) = 6 ; {VA = BKUP06} ; IF (MONTH_OF (REF_PER) = 7 ; {VA = BKUP07} ; IF (MONTH_OF (REF_PER) = 8 ; {VA = BKUP08} ; IF (MONTH_OF (REF_PER) = 8 ; {VA = BKUP08} ; IF (MONTH_OF (REF_PER) = 9 ; {VA = BKUP09} ; IF (MONTH_OF (REF_PER) = 10 ; {VA = BKUP10} ; IF (MONTH_OF (REF_PER) = 11 ; {VA = BKUP11} ; IF (MONTH_OF (REF_PER) = 12 ; {VA = BKUP12}; IF(EXIST({VA = BKUP01}); {VA = BKUP01}; {VA = 2} ; IF(EXIST({VA = BKUP02}); {VA = BKUP02}; {VA = 2}; IF(EXIST({VA = BKUP03}); {VA = BKUP03}; {VA = 2}; IF(EXIST({VA = BKUP04}); {VA = BKUP04}; {VA = 2}; IF(EXIST({VA = BKUP05}); {VA = BKUP05}; {VA = 2}; IF(EXIST({VA = BKUP06}); {VA = BKUP06}; {VA = 2}; IF(EXIST({VA = BKUP07}); {VA = BKUP07}; {VA = 2}; IF(EXIST({VA = BKUP08}); {VA = BKUP08}; {VA = 2}; IF(EXIST({VA = BKUP09}); {VA = BKUP09}; {VA = 2}; IF(EXIST({VA = BKUP10}); {VA = BKUP10}; {VA = 2}; IF(EXIST({VA = BKUP11}); {VA = BKUP11}; {VA = 2}; IF(EXIST({VA = BKUP12}); {VA = BKUP12}; {VA = 2})))))))))))))))))))

Many thanks in advance.

Célia Guerra

[Olga_Vasileva]

Hi celia_guerra , in case you haven't solved it yet, you're missing 6 closing brackets at the end. For such long formula expressions you can double-check the brackets in Notepad++.

[former_member681376]

I managed to do it. Thanks anyway.

IF (MONTH_OF (REF_PER) = 1 ; {VA = BKUP01} ; IF (MONTH_OF (REF_PER) = 2 ; {VA = BKUP02} ; IF (MONTH_OF (REF_PER) = 3 ; {VA = BKUP03} ; IF (MONTH_OF (REF_PER) = 4 ; {VA = BKUP04} ; IF (MONTH_OF (REF_PER) = 5 ; {VA = BKUP05} ; IF (MONTH_OF (REF_PER) = 6 ; {VA = BKUP06} ; IF (MONTH_OF (REF_PER) = 7 ; {VA = BKUP07} ; IF (MONTH_OF (REF_PER) = 8 ; {VA = BKUP08} ; IF (MONTH_OF (REF_PER) = 8 ; {VA = BKUP08} ; IF (MONTH_OF (REF_PER) = 9 ; {VA = BKUP09} ; IF (MONTH_OF (REF_PER) = 10 ; {VA = BKUP10} ; IF (MONTH_OF (REF_PER) = 11 ; {VA = BKUP11} ; IF (MONTH_OF (REF_PER) = 12 ; {VA = BKUP12})))))))))))))

[adelaide_brunel]

Hi Celia,
Indeed, your IF conditions need to be all included in the same expression.
I'm glad to know the formula is now working fine.
Regards

Adélaïde

[former_member681376]

Thank you Adelaide.

Last bracket was missing.

My question was more what is the syntax for multiple IFS.

Many thanks

Célia

[adelaide_brunel]

Hi Celia,

Can you try with the following syntax : IF(MONTH_OF (REF_PER) = 1; {VA = BKUP01}) ?

Regards

Adélaïde