Pivot/grouping + charting question

[pkz]

Hi,

Is it possible to do charting in pivot table?

For example in this example: https://www.htmlelements.com/demos/pivottable/selection-details/

is it possible to chart the selection?

 

[admin]

Hi,

The only charting in combination with pivot is available here: https://www.htmlelements.com/demos/pivottable/integration-with-chart/

Best regards,
Peter Stoev

Smart UI Team
https://www.htmlelements.com/

[pkz]

Hi,

 

Thank you for the response! That was helpful!

I’m not able to convert a grid to pivot.

Here is the code: https://codepen.io/ttwer2/pen/ZEjxBzp

 

I followed the example of converting a table to pivot, but I think I may be missing something. Can you please help?

[admin]

Hi pkz,

The provided example has an error – smartPivotTable: At least one column with ‘summary’ is required.”

Best regards,
Peter Stoev

Smart UI Team
https://www.htmlelements.com/

[pkz]

Great! Thank you – that fixed it!

Updated working code: https://codepen.io/ttwer2/pen/ZEjxBzp

Few questions for pivot:

1. Is it possible to add multiple summaries of the same column? for example, sum(expenses) as one statistic and avg(expenses) as another?
2. Are we able to define custom stats/calculations for the summary?
3. I’m not able to use the conditional formatting mode in the above code – am I missing something? i.e nothing happens when I click the button on the top right corner
4. In pivot mode > filters, I’m only able to see the numbers as categorical values where I can select or de-select them. How can this be changed to behave as per the type of column? i.e if number -> then range or if text, then string matching
5. How can I sort the columns in the pivot? for example, the stdevp(income) and the group in the pic below [Solved]
6. Is it possible (programmatically) to plot the selection in a pivot via charting capabilities of htmlelements? example:
   this would come up in a chart with X-axis showing USA,AUS,UK and y-axis showing stddevp(income)
   this would be an excellent feature and I’d be grateful and go for the license if you can help with this! 🙂

Thank you!

• This reply was modified 1 year, 3 months ago by pkz.

[ivanpeevski]

Hi pkz,

 

1.  At the moment, we support multiple different summaries only for the smart-grid component.
2.  To achieve this, you could use the onCellRender() callback function. The function is called before each cell render and you can use it to perform the custom calculations and set them as value. Here is a simple example, which changes the value of the ‘USA Income’ summary: codepen
3.  The button is inactive since the div.switchContainer is standing on top of it. If you change it’s position, the button will be active
4. At the moment, the pivot designer only supports the categorical filter, however this is a great suggestion, and we will consider adding such option in a future release!
5. Yes, it’s possible – the createChart() function simply passes the table data to the smart-chart component. You can create a custom function, which will pass only the selected data – please have a look at the example here codepen
   We also have a similar demo on our site here: https://www.htmlelements.com/demos/pivottable/integration-with-chart/

Best regards,

Ivan Peevski

Smart UI Team
https://www.htmlelements.com/

[pkz]

Hi,

Thank you for your response and help!

1. No problem
2. I don’t understand your solution here. What I would like to see is, for example, a multiplier of x2 for every number of Income. Say, I define a function foo(x){x*2} and apply it to all the column’s values, and I foo to be a list of summary types like sum, stddev etc., – is this possible? so basically my own summary functions
3. Got it – thanks for indicating this!
4. Thank you! This would definitely be a HUGE addition!
5. This is great progress. I just have a few questions regarding this:
   1. When the chart is generated, I often see empty axis values:|
      
      But this clears up on clicking on the legend again – is there a way to make sure they are always displayed correctly?
   2. I noticed that in your example you have used income as an example, what if I want to support other chart types with say, multiple datasets, how can I easily change the chart type of smart-chart (i.e #pivot-chart)?

 

 

[ivanpeevski]

Hi pkz,

For the custom summary function:

Please see the example here: codepen – it implements a custom summary function which sums 2* value of the data
I have left some comments to make it easier to understand how it works and how to modify it.
Though since this is not an official feature and more of a workaround, there might be some edge cases I have missed where bugs happen.

 

And for the Charting:

Thanks for noting that! I updated the code with a fix here – codepen
Also added some comments to make some of the functions clearer.

For example, here is the result when you select Country + Income + Expenses

You can also visit the smart-chart demos page to see some of the possible chart types and customization for the chart.

 

Best regards,
Ivan Peevski

Smart UI Team
https://www.htmlelements.com/

 

[pkz]

Hi,

Thank you for the code!

The custom function modification is not exactly what I imagined it to be, but I will inspect the source since I just purchased the license.

Regarding the charting, I’m still seeing a lot lesser occurrences of the chart with no labels. Can you please let me know what change you made or what causes this issue? I saw the code and the logic change seems to be related to multi-bar charting/data management.

Regarding the multi bar, is it possible to chart this:

If I chart this, it comes up with an empty chart.

 

Thank you for your help!

 

[ivanpeevski]

Hi pkz,

Thanks for reporting that! Here is the update example with the fix included – codepen

If the chart is empty, there is most likely some error when transforming the data from the pivot to the chart

 

Best regards,
Ivan Peevski

Smart UI Team
https://www.htmlelements.com/

[pkz]

Hi,

Thank you for the update. The fix seems to be partial has different ways of selection have different results and I’m still seeing the empty labels sometimes – it appears only when the chart moves. Is there any way to “redraw” or “refresh” the chart?

In this video, I’ve shown 2 issues:

1. The labels not appearing unless I move the chart box
2. The chart not displaying data when the _same_ selection is made slightly differently (same data, but different ways of selection)

Video: https://jmp.sh/NW90Ealy

Can you please help with this?

[ivanpeevski]

Hi pkz,

 

Thanks for the feedback!

Here is the updated version: codepen

The Chart can be refreshed by calling smartChart.refresh(), and the animation is controlled with the ‘animation’ property.

 

Best regards,
Ivan Peevski

Smart UI Team
https://www.htmlelements.com/

 

 

[pkz]

Hi,

 

Thank you again for the help! Everything is perfect now!

One small question: Is it possible to change the pivot-chart type dynamically?

 

window.Smart(
"#pivot-chart",
class {
get properties() {
return {
caption: "",
description: "",
xAxis: {
dataField: "country",
gridLines: {
visible: false
},
type: "basic"
},
colorScheme: "scheme28",
seriesGroups: [
{
type: "column",
columnsGapPercent: 50,
seriesGapPercent: 0,
valueAxis: {
description: "Revenue",
axisSize: "auto"
},
series: []
}
]
};
}
}
);

This is used in the pivot group charting. What if I wanted to dynamically have buttons like line, bar, chart etc.? does it involve changing the data again? or is there a way to pass a parameter to the smart-chart

i.e: document.querySelector("smart-chart").dataSource = chartDataSource; so that we can easily switch from line or bar?

 

Thanks again for all your help! Really liking using the library so far!

 

 

[ivanpeevski]

Hi pkz,

The object “smartChart.seriesGroups[0]” controls the settings of the chart’s type.

In the example, the type is set ‘column’. You can change the type just by changing it to ‘line’ or ‘area’.

To create a bar chart – set the type to ‘column’ and set ‘orientation’ to ‘horizontal’

 

Best regards,
Ivan Peevski

Smart UI Team
https://www.htmlelements.com/

 

[pkz]

Hi,

 

I understand the parameters of the smartChart object/class but what I wanted to know was if it was possible to dynamically assign the type. Here is an example of what I would like to do:

 

function process_selection(chart_type){

// assume this function gets the grid’s selection and processes the data so that it can be used for charting – i.e the code inside the chart’s onclick in your codepen.

//how can I use the chart_type variable here to change the properties of the already defined window.Smart("#pivot-chart",...) ? (since this is what we are using to draw on)

//the obvious solution is to re-define the entire smart object with the new chart type – but this seems suboptimal – am I understanding the concept of smart objects correctly?

}

With this function, I can assign to onClick on different chart types:

process_selection('line')

process_selection('bar')

etc.,

[Author]

Posts