cancel
Showing results for 
Search instead for 
Did you mean: 

Using a calculation for a dynamic column name

Former Member
0 Kudos

Hello all,

I want to use dynamic names for colums in one of my tables. The names should be determined based on values entered in the drop down box of fiscal year. I have used the following formula:

"ACT " & @fiscal_year

This is working fine, f.e. if I select 2006, the column name shows ACT 2006.

However I also have 1 column that should display the value of the previous year. Thus if I select 2006, it should show ACT 2005. However I cannot do any mathematics with fiscal year because it is a text field.

How can I adopt the formula to this requirement?

Thanks in advance,

Ralph

Accepted Solutions (0)

Answers (1)

Answers (1)

martin_wolpers
Explorer
0 Kudos

hi again ralph,

you have several possibilities, all of them a somehow wiered concatenation of text, numeric or date formating expressions.

a simple way to do that would be to pass the text into a numeric value using NVAL(text), the do a substraction of one (from 2006 to 2005) and than pass it back to text using NSTR(n, mask), <use "" for mask>.

another way would be to pick the year as dateformat, do a date-add using DADD() with negativ addition (i.e. -1, "YY") for decreasing one year and then passing the date into a text-value.

regards,

Martin

Former Member
0 Kudos

Hi Martin,

I tried the first solution but it gives me a ACT 2.005,00 value. I do not want to see the dot and comma.

The second one does not seem to work either. I get ACT 31.12.1898. Could you write the whole formula for me down?

Thanks in advance,

Ralph

Former Member
0 Kudos

Hi Ralph,

you either have to cast the value to integer, using INT(value).

To correct the strange value 31.12.1898, you might have to change the order of DDMMYYYY.

Best Regards,

Benni

martin_wolpers
Explorer
0 Kudos

Hi Ralph,

from your reply I take it that you start with a text-field rather than a date-field. Makes things a lot easier.

the problem you encountered comes from the portal setting for numbers (including the thousand separator. Just add an < "normal" > instead of the < "" > for the mask in the NSTR() and the display of the year will be fine. Im adding the whole sequence of the expression, with @date being the field containing the original year as text: nstr(nval(@date) - 1 ,"normal")

have fun,

Martin

Message was edited by:

Martin Wolpers