topic Re: Visualization API - Numeric formatting in pivot table in Integration, Extension & APIs

Visualization API - Numeric formatting in pivot table

rbartley — Thu, 23 Aug 2018 16:40:55 GMT

Hi everyone,

I am having an issue when trying to format a measure field when creating a pivot table visualization on-the-fly. The definition of the measure is:

{

"qDef": {

"qLabel": "Value",

"qDef": "Sum(Value)",

"qNumFormat":{

"qType":"F",

"qnDec": 2,

"qUseThou": 1,

"qFmt": "# ##0",

"qDec": ".",

"qThou": " "

}

I would like to format the number so that it shows the thousand separator as a space and displays 2 decimal places, but I have tried various options and it is always displayed with 3 decimal places and no thousand separator. I assume that hs qNumFormat is being ignored for some reason. Anyone have any ideas?

Thanks in advance.

Re: Visualization API - Numeric formatting in pivot table

ThomasGorr — Thu, 23 Aug 2018 20:52:37 GMT

Hi Richard, where did you change the settings of qDef? If you've set them in the initialProperties, you need to remove the extension and add it to the sheet again. If you didn't, please provide your code in more detail.

Re: Visualization API - Numeric formatting in pivot table

ErikWetterberg — Fri, 24 Aug 2018 06:42:18 GMT

Hi,

Have you tried settings this format in the client and then using /options/developer to see the property structure?

Erik Wetterberg

https://extendingqlik.upper88.com/

Re: Visualization API - Numeric formatting in pivot table

rbartley — Fri, 24 Aug 2018 14:48:50 GMT

Hi Thomas,

Thanks for responding. Sorry, I should have specified that I am doing this in a mashup, not in an extension.

Re: Visualization API - Numeric formatting in pivot table

rbartley — Fri, 24 Aug 2018 14:49:51 GMT

Hi Erik,

Thanks for replying. As per my response to Thomas, I am doing this in a mashup.

Re: Visualization API - Numeric formatting in pivot table

rbartley — Fri, 24 Aug 2018 15:59:26 GMT

Even though I copied the definition in my script from the Qlik Help page

https://help.qlik.com/en-US/sense-developer/June2018/Subsystems/APIs/Content/CapabilityAPIs/VisualizationAPI/pivottable-…

and is as follows:

"qDef": {

"qLabel": "Value",

"qDef": "Sum(Value)",

"qIsAutoFormat": false,

"qNumFormat": {

"qType": "F",

"qnDec": 2,

"qUseThou": 0,

"qFmt": "#,##0.00",

"qDec": ".",

"qThou": ","

}

When I output the created visualization object to the console. I can see the following

qMeasureInfo: Array(2)
0 : 
autoSort: true
cId: "ykyyGCs"
numFormatFromTemplate: true
qApprMaxGlyphCount: 14
qAttrDimInfo: []
qAttrExprInfo: (2) [{…}, {…}]
qCardinal: 0
qFallbackTitle: "Value"
qIsAutoFormat: false
qMax: 17857575117.98
qMin: -48318.507
qNumFormat: 
qDec: "."
qThou: ","
qType: "F"
qUseThou: 0
qnDec: 3
__proto__:Object
qSortIndicator: "D"
__proto__: Object

It seems as though my settings are being overridden.

Re: Visualization API - Numeric formatting in pivot table

rbartley — Fri, 24 Aug 2018 16:33:53 GMT

BTW, I am using the April 2018 version of Qlik Sense

Re: Visualization API - Numeric formatting in pivot table

ErikWetterberg — Sat, 25 Aug 2018 06:08:18 GMT

‌Hi,

yes I got that. This feels like a bug. You could try Settings the format with setOptions https://help.qlik.com/en-US/sense-developer/April2018/Subsystems/APIs/Content/CapabilityAPIs/VisualizationAPI/setOptions… but that’s more of a workaround.

Erik Wetterberg

Re: Visualization API - Numeric formatting in pivot table

ErikWetterberg — Mon, 27 Aug 2018 12:20:17 GMT

Hi,

From the documentation, there seems to be two other options that can be relevant:

numFormatFromTemplate

Boolean

When enabled, the number format to use can be selected from multiple predefined formats based on the desired type: number or date.

Default: true

isCustomFormatted

Boolean

Set to true to toggle off the default client formatting.

Have you tried those?

Erik Wetterberg

https://extendingqlik.upper88.com/

Re: Visualization API - Numeric formatting in pivot table

rbartley — Mon, 27 Aug 2018 16:21:38 GMT

Hi Erik,

Thanks for responding again. Yes, I have tried those two options, so I'll look into your idea of using setOptions and I'll let you know whether this resolves the issue.

Re: Visualization API - Numeric formatting in pivot table

rbartley — Tue, 28 Aug 2018 11:21:38 GMT

Hi Erik,

The setOptions method worked.

var strPivotMeasureFormat = {
"qHyperCubeDef": 
{
"qMeasures": 
[
{
"qDef": {
"qLabel": "Value",
"qDef": "Sum(Value)",
"qIsAutoFormat": false,
"qNumFormat": 
{
  "qType": "F",
  "qnDec": 2,
  "qUseThou": 0,
  "qFmt": "# ##0.00;0;",
  "qDec": ".",
  "qThou": " "
}
}
}
]
}
};

....

app.visualization.create(
"pivot-table",
strPivotDef,
                  {  
                    "qHyperCubeDef":{  
                         "qNoOfLeftDims": 7,
"qAlwaysFullyExpanded":true,
"qIndentMode":false  
                    },  
                    "showTitles": true,  
                    "title": strDataExplorerTableTitle + " " +strSelections
                  }  ).then(function(pivot)
  {
  console.log('pivot',pivot);
  pivot.show('pivotExplorer');
  pivot.setOptions(
strPivotMeasureFormat
);