jqxPivotGrid computed column

[jbrahyParticipant]

How would I make a computed column to calculate ecpm? where the formula is ecpm = ((total revenue / ad units) * 1000)

I saw this example but I don’t know how to get the data for the row since the row has a column for total_revenue and ad_units. I just see values[i].

customAggregationFunctions: {
                'ecpm': function (values) {
                    if (values.length <= 1)
                        return 0;

                    var sum = 0;
                    for (var i = 0; i < values.length; i++)
                        sum += values[i];

                    return sum;
                }
            }

[TodorParticipant]

Hello jbrahy,

Please review the following example whether it fits your needs.

Let us know if you need further assistance.

Best Regards,
Todor

jQWidgets Team
https://www.jqwidgets.com

[jbrahyParticipant]

I need the row values to compute the value for the column. How do I access those?

[TodorParticipant]

Hello jbrahy,

In cellsRenderer function there are getSpecificRow and getSpecificColumn functions. When you combine them, as it is shown below, you would be able to get a cell’s value (var itemsSumCell = myPivotGridCells.getCellValue(specificRow, specificColumn);).

            cellsRenderer: function (pivotCell) {
                var getSpecificRow = function (cells, rows, columns, id) {
                    var specificRow = null;
                    for (var i = 0; i < myPivotGridRows.items.length; i++) {
                        var currentRow = myPivotGridRows.items[i]
                        var currentRowInnerItems = currentRow.items;
                        if (1 < currentRowInnerItems.length && currentRow.id == id) {
                            specificRow = currentRow;
                            break;
                        }
                    }

                    return specificRow;
                };

                var getSpecificColumn = function (columnsArray, key) {
                    var column = null;
                    for (var i = 0; i < columnsArray.length; i++) {
                        if (myPivotGridColumns.visibleLeafItems[i].text == "Items sum") {
                            var currentColumn = columnsArray[i];
                            var currentColumnKey = currentColumn.parentItem.text;
                            if (currentColumnKey == key) {
                                column = myPivotGridColumns.visibleLeafItems[i];
                                break;
                            }
                        }
                    }

                    return column;
                };
                
                var myPivotGridCells = $('#divPivotGrid').jqxPivotGrid('getPivotCells');
                var myPivotGridRows = $('#divPivotGrid').jqxPivotGrid('getPivotRows');
                var myPivotGridColumns = $('#divPivotGrid').jqxPivotGrid('getPivotColumns');
                
                var cellText = pivotCell.value == 0 ? '' : pivotCell.formattedValue;

                var weightAverageValue = -1;
                if (pivotCell) {
                    if (pivotCell.pivotColumn.text == "Weighted Average") {
                        var cellParentColumnKey = pivotCell.pivotColumn.parentItem.text;

                        // GetSpecific Row
                        var specificRow = getSpecificRow(myPivotGridCells, myPivotGridRows, myPivotGridColumns, 2);

                        // GetSpecific Column
                        var specificColumn = getSpecificColumn(myPivotGridColumns.visibleLeafItems, cellParentColumnKey);
                        
                        if (pivotCell.pivotRow.items.length == 1) {
                            cellText = '';
                            // TODO: You could add calculated weight average for one item
                        } else {
                            var parentItem = pivotCell.pivotColumn.parentItem;
                            var itemsSumCell = myPivotGridCells.getCellValue(specificRow, specificColumn);
                            var itemsSumCellValue = itemsSumCell.value;
                            var calculatedValue = pivotCell.value / itemsSumCellValue;
                            cellText = Math.round(calculatedValue * 100) / 100;
                        }
                    }
                }
                
                return "<div style='width: calc(100%-8px); height: 100%; padding: 4px; margin: 0px;'>" + cellText + "</div>";
            }

Best Regards,
Todor

jQWidgets Team
https://www.jqwidgets.com

[jbrahyParticipant]

Thank you. This is exactly what I needed!

[Author]

Posts