How to convert YEAR MM row data to readable localized month names

6 posts / 0 new
Last post
Anders
Anders's picture
How to convert YEAR MM row data to readable localized month names

Business Analyze 4 uses the database for all its sorting operations. For the same reason the developer often formats month, quarter, week etc columns accordingly.

A example could be the format (2011 03) which represent march 2011.
This format is excellent for for database sorting scenarios but is not neat when it is displayed in a table column or in a chart axis.

The Scripting Engine parser is the solution

 

Open the "Edit Column" dialog and select the "Expression" tab in the top of the dialog.

Given the format above we can use the =eval(); and =locale(); functions together to reformat the value to a human readable format of choice.
This post will reformat the value "2011 03" to "March 2011":

Fire up your Scripting Engine Expression parser and follow these simple steps

Start with splitting the row value into an array:

=eval("2011 03".split(" "));

 

The outpu area will now output a JavaScript array serialized as string "2011,03"

Extend you expression to retrieve only the month part of the array:

=eval("2011 03".split(" ")[1]);

 

The output area will now output a string: "03"

The BA4 locale files contains localized month names with the key month01-month12 (Full month names) and m01-m12 (short month names).
By feeding the output from the above expression into a new locale-function we can get a localized name of the month:

=locale(month=eval("2011 03".split(" ")[1]););

 

The output area will now show the string "March" if your current locale is English.

Once you have got the correct mont name, you might want to get the year part of your column value appended to your month name.
This can be easily added after the expression you just created but now you pick the first item from the array of strings:

=locale(month=eval("2011 03".split(" ")[1]);); =eval("2011 03".split(" ")[0]);

 

The output area will now show the string "March 2011" if your current locale is English.
Next step will be to provide the actual column value from each row into the expression.
Rowdata is implemented into the expression by simply referencing the column name in square brackets: [ColumnName]:

=locale(month=eval("[ColumnName]".split(" ")[1]);); =eval("[ColumnName]".split(" ")[0]);

 

Attn! Remember to leave the quotation marks around the column reference since the value from the dataset are inserted into the expression before the Scripting Engine Expression parser gets to play with your expression.

Ingar
column name with brackets in it

Hi

 I get from query value "2011 01". Column is named Saledate(Year-Month). As you see, in column name I use brackets. When I try to use those in my scripts, it does not work properly. Should I give another alias name or is there a workaround.

Another question, if query returns value "2011 M01", is there a workaround to get rid of M as well or should I take care of this in my query.

=locale(month=eval([Saledate(Year-Month)]split(" ")[1]););

Anders
Anders's picture
Hi Ingar,

Hi Ingar,

We do support column names with brackets as long as they are wrapped with square brackets.
I guess that you have already seen the bug in the above script:
- The [columnName] should be wrapped in quotation marks since the expression parser parses the inner sections first:
  - After the column name is replaced with the actual value the script becomes: =eval(2011 01.split(" ")[1]); which will give you a javascript syntax error since 2011 01 is a string

I see that your next reply has fixed this issue.

As for the question of if you can remove the M from 2011 M01:
You can either use the =eval(); function by implementing the javascript string.replace function or you can change the query in the datacentral.

Example:
=eval("[columnName]".replace(/M/g, ""));    // Using RegExp
or
=eval("[columnName]".replace("M", ""));    // Using ordinary string replace

Ingar
column name with brackets in it

Hi again

Some additional information to my previous comment/question:

I use the following script to get only month names. It works in scripting engine, but when I try to use it in analyzis editor, script fails. It keeps telling that missing language string. Where should I put this language string?

=locale(month=eval("[SaledateYM]".split(" ")[1]););

Anders
Anders's picture
It seems like there could be

It seems like there could be some kind of mismatch between what the database actually returns and what you were testing in the scripting engine tester.

try to add the ordinary column value output as well som you can validate that the column returns the same format as you expect:
=locale(month=eval("[SaledateYM]".split(" ")[1]););
=eval("[SaledateYM]");

Erik
Erik's picture
For SuperOffice Analyze you may use these...

For the SuperOffice Analyze and SuperOffice Analyze for Customer Service packages, the data tables returns the Year-Month date columns like 2013 M02. Which means February 2013. Therefore the expression must be adjusted slightly. Any of these will work fine:

=locale(month=eval("[ColumnName]".split(" M")[1]);); =eval("[ColumnName]".split(" M")[0]); 

returns: February 2013

=eval("[ColumnName]".split(" M")[0]); =locale(month=eval("[ColumnName]".split(" M")[1]););

rerurns: 2013 February

If the column only returned a number from 1 - 12 for the month and you want to dispaly the localized name you could use this expression:

=locale(month=eval(("0" + "[Monat]").substr(0,2)););

PS! The language used for the name of the month will always match the locale setting for your user in Business Analyze.

Log in or register to post comments