Finding solution to "Missing ';' or closing parenthesis in function 'If' at position 640. (IES 10063)" in Webi

Question

I am trying to build a dimension in webi to look up someone's 65th birthday to attach the appropriate retirement fund label for our pension company.

Replace did not work for more than 2 so I tried to use If. Having built it I am getting error of IES 10063 . I have checked that each part has a closing parenthesis, and tried breaking it down, but each time get the same error and am now stuck. Would anyone be able to suggest how to resolve this issue please?

= if([65 birthday]>01/07/2016;if([65 birthday]<30/06/2020;if([65 birthday]>01/07/2020;if([65 birthday]>30/06/2025;if([65 birthday]<01/07/2025;if([65 birthday]>30/06/2030;if([65 birthday]>01/07/2030;if([65 birthday]<30/06/2035;if([65 birthday]>01/07/2035;if([65 birthday]>30/06/2040;if([65 birthday]<01/07/2040;if([65 birthday]>30/06/2045;if([65 birthday]>01/07/2045;if([65 birthday]<30/06/2050;if([65 birthday]>01/07/2050;if([65 birthday]>30/06/2055;if([65 birthday]<01/07/2055;if([65 birthday]>30/06/2060;if([65 birthday]>01/07/2060;if([65 birthday]<30/06/2065;"BE13");"BE23");"BE33");"BE43");"BE53");"BE63");"BE73");"BE83");"BE93");"BE03")

Thanks Duncan

Answer 1

I am sure it is possible to come up with the logically correct statement in the format with which you have started. However, I would suggest a different approach that is more readable and as a result more maintainable.

=if([65 birthday] Between ('07/01/2020'; '06/30/2025'); "EB13";
 if([65 birthday] Between ('07/01/2025'; '06/30/2030'); "EB23";
 if([65 birthday] Between ('07/01/2030'; '06/30/2035'); "EB33";
 if([65 birthday] Between ('07/01/2035'; '06/30/2040'); "EB43";
 if([65 birthday] Between ('07/01/2040'; '06/30/2045'); "EB53";
 if([65 birthday] Between ('07/01/2045'; '06/30/2050'); "EB63";
 if([65 birthday] Between ('07/01/2050'; '06/30/2055'); "EB73";
 if([65 birthday] Between ('07/01/2055'; '06/30/2060'); "EB83";
 if([65 birthday] Between ('07/01/2060'; '06/30/2065'); "EB93"; "EB03")))))))))

Now I may not have the ranges and values exactly as you require. However, in this format those changes can made quite easily.

Of course, this will only work long-term if those labels never change. If they do change as time moves you will constantly be having to update this formula. If that is the case I have something else to suggest. It is much more complicated so I don't want to go there if it is not necessary.