topic Dynamic Chart Macro and Number Format Problem in QlikView

Dynamic Chart Macro and Number Format Problem

Fri, 15 Jul 2011 08:05:12 GMT

Hi guys,

I have used a macro flying around on the forum for a dynamic chart that gets updated depending on the dimension selected. This works fine.

The problem I now have is that I get my numbers displayed as 10000000.

I managed to do something in the macro as suggested here in http://community.qlik.com/message/9628#9628 to get my numbers showing as 1,000,000,000.

See code snippet from the link provided

set chart = ActiveDocument.Sheets("Main").CreateStraightTable

chart.AddDimension "ProductType"

chart.AddExpression "sum(Amount)"

set p = chart.GetProperties

set np = p.Expressions.Item(0).Item(0).Data.ExpressionVisual.NumberPresentation

np.Dec = "."

np.Fmt = "# ##0.00"

np.nDec = 2

np.Thou = " "

np.Type = 11 'fixed decimal

np.UseThou = 1

chart.SetProperties p

The problem I have is that I would like the macro to display my values as 1b instead of 1,000,000,000 and 1m instead of 1,000,000 and 1k instead of 1,000

Does anyone know how to get this to work in the macro? Apologies for my vague question, as I don't know much about macros.

Thanks

Dynamic Chart Macro and Number Format Problem

kaushiknsolanki — Fri, 15 Jul 2011 08:20:13 GMT

Hi,

Try to include below macro.

set chart = ActiveDocument.Sheets("Main").CreateLineChart

chart.AddDimension "ProductType"

chart.AddExpression "sum(Amount)"

set p = chart.GetProperties

p.Expressions.Item(0).Item(0).Data.AxisData.Units.Unit.v = "$"

p.Expressions.Item(0).Item(0).Data.AxisData.Units.kUnit.v = "1000$"

p.Expressions.Item(0).Item(0).Data.AxisData.Units.MUnit.v = "million$"

p.Expressions.Item(0).Item(0).Data.AxisData.Units.GUnit.v = "billion$"

chart.SetProperties p

Regards,

Kaushik Solanki

Re: Dynamic Chart Macro and Number Format Problem

Fri, 15 Jul 2011 08:39:11 GMT

Thanks a lot Kaushik, that worked.

Few more questions please.

1. I tried the exact code in a pivot table, but it does not work, do you know of anyway to get it working a pivot table also?

2. How can I get some sort of documentation regarding the properties you used and on macros in QlikView, as the only thing available is the API guide?

3. Is it possible to also set the font type and size, say for example, font type Calibri and font size 9?

Thanks a lot

Re: Dynamic Chart Macro and Number Format Problem

kaushiknsolanki — Fri, 15 Jul 2011 09:04:19 GMT

Hi,

Everything is there in API giude.

There is not other documentation for the API guidelines.

At my side it is working in pivot table too..

Upload the qvw file.

Regards,

Kaushik Solanki

Re: Dynamic Chart Macro and Number Format Problem

Fri, 15 Jul 2011 10:52:06 GMT

Everything is in order now.

Thanks a lot