Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Dynamic Chart Macro and Number Format Problem

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

1 Solution

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

4 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

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

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

Everything is in order now.

Thanks a lot