topic Re: Macro number format thousand separator in QlikView

Macro number format thousand separator

1000qlik — Wed, 29 Jan 2014 10:52:56 GMT

Dear all,

I am using macros to export some tables to Excel and apply a specific format.

I am struggling with the thousand separator in the number formatting.

It should be a single quote ' and should apply to thousands and millions, if any.

Examples:

222111000 should be 222'111'000
1000 should be 1'000
999 should be 999 (the same)

I have a line code like this:

objExcelDoc.Sheets(sheetName).Columns("C:K").EntireColumn.NumberFormat = "#'##0;(#'##0)"

But the result in this:

222111'000 (missing millions separator)
1'000 (ok)
'999 (no single quote should appear)

When I look at the format in Excel, there are "\" automatically added that I think are causing the issue: #\'##0;(#\'##0)

Any help would be greatly appreciated.

Thanks,

Robert

Re: Macro number format thousand separator

marcus_sommer — Wed, 29 Jan 2014 12:25:11 GMT

I think this is only possible with a conditional formating, try this:

[>=1000000]#'###'##0;[>=1000]#'##0;#

- Marcus

Re: Macro number format thousand separator

1000qlik — Wed, 29 Jan 2014 13:13:50 GMT

Hi Marcus,

Thank you for your reply. The conditional formatting you provide works perfectly.

I would now like to format negative values within brackets and still apply the same logic for thousand separators.

Is that possible?

Thank you for your help!

Robert

Re: Macro number format thousand separator

jonathandienst — Wed, 29 Jan 2014 13:24:35 GMT

You can, but I have found the setting a little flakey. I copy the format string to the clipboard, set the thousands separator and decimal separator (even though the are correct), and then paste the format string back. Hey presto! and it works.

HTH

Jonathan

Re: Macro number format thousand separator

1000qlik — Wed, 29 Jan 2014 13:37:45 GMT

Hi Jonathan,

As long as it works I'm happy

Would you have a sample code that achieves this?

Thanks,

Robert