handling timezones and formats for date/time,number and money

[andre_ficken]

Hi there fellow Communitors,

I am currently working on a challenging project where the challenge lies in supplying the same information to people across the globe in various time zones and languages. Across the globe here is from EU to US/Latin-America to ASIA, Languages also include NLD,ENU,DEU,FRA,ITA,ESP,CHI. From a timezone/date perspective date and numeric values are taken care of. The challenge lies in the formatting of dates, numbers and money....

A little explanation on date: Europe would like to see dates as D-M-YYYY, where US looks at dates as M-D-YYYYY and Asia prefers YYYY-MM-DD.

For numbers we have a similar situation where Europe uses a comma for decimal separator and a point as thousand separator. For the US users this has to be reversed... comma for thousands and point for decimal. Asia follows Europe as far as I am aware.

The money is a different cookie... we have the numbers like Turnover and cost in € and local currency (local currency is dictated by the local company reporting currency), this can be US$, GB pounds and so forth. We also need numbers in 1s, 1.000s and 1000.000s

For the presentation side of things we would like to have the currency sign in the money format,

The thousand and decimal separations and the date format setting depending on a user/company preference that is included in section access. And it has to be, and this is the real problem area, in 1 single dashboard. There are various dashboards by discipline, but for example the finance dashboard is used across the globe and we do not want to have to create dashboards for every different setting.

Finally we have to cover the exporting side of things too, since all local companies use Excel to export data from Qlik. Since Excel uses local settings we would need Qlik to seamlessly insert the data from Qlik into a worksheet. We have been testing with different approaches.... using num() (causes export to Excel to loose formatting)  and also changing the numeric notations (expression default caues the export to just dump all numbers).

Help and ideas in all areas is very much appreciated. The company is also looking to switch from local office installations to Office365 based on the 2016 version... I have tested this already with a local installation, but this fails until now..Excel does not start en no export is done... There is another ticket here explaining that.

[marcus_sommer · Accepted solution]

I think the formatting-topic could be solved by applying formatting-functions to each of your expressions and the using of variables and/or fields for the format-strings which are referenced to a field in which the user choose its preferred settings respectively its country. I mean something like this:

num(sum(value) / $(vDivisor), '$(vFormat)')

with vFormat:

pick(match(CountrySelect, 'NLD','ENU','DEU','FRA','ITA','ESP','CHI'),

     'Format1',

     'Format2',

     ...)

by using the variable-approach and by a field-control something similar to this: Handling Multiple Languages which I would probably prefer.

The export to excel is a different matter and I'm not sure that you could solve it sufficient with the native qlikview features. Formatting an expression per number-tab will return a numeric value with its format but using format-functions like num/date/money will return a string.

By using the desktop client or the IE plugin you will be able to use macros for the export and with them everything could be adjusted like it should be. By using the AJAX client I don't know if it would be possible to address all your needs - whereby I could imagine to use an own developed global excel add-on which per click or shortcut adjust the output to the local country formatting.

- Marcus

[andre_ficken]

I think we will start exploring this route. To me this has the most potential as a solution.