jQuery UI Widgets Forums General Discussions jqxPivotGrid computed column

This topic contains 4 replies, has 2 voices, and was last updated by  jbrahy 1 week, 4 days ago.

Viewing 5 posts - 1 through 5 (of 5 total)
  • Author
  • jqxPivotGrid computed column #105174

    jbrahy
    Participant

    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;
                    }
                }
    • This topic was modified 2 weeks, 1 day ago by  jbrahy.
    • This topic was modified 2 weeks, 1 day ago by  jbrahy.
    jqxPivotGrid computed column #105194

    Todor
    Participant

    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

    jqxPivotGrid computed column #105200

    jbrahy
    Participant

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

    jqxPivotGrid computed column #105215

    Todor
    Participant

    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

    • This reply was modified 1 week, 5 days ago by  Todor.
    • This reply was modified 1 week, 5 days ago by  Todor.
    jqxPivotGrid computed column #105240

    jbrahy
    Participant

    Thank you. This is exactly what I needed!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic.