Forum Discussion

Martha's avatar
Martha
Cloud Apps
02-05-2025
Solved

Replacing Total with different column in Pivot2

Hi, I have a pivot2 table and some trouble calculating the correct Totals, due to many-to-many joins. However, I can tweak the data model, so that either the values OR the Total is correct, and can ...
  • AssafHanina's avatar
    02-08-2025

    Hey Martha ,

    Please refer to sample Pivot widget script. 
    the Script store the grand total(Cell.content) of the First Value column, (Index 1 in the example) and replace it with the Grand total of Index 2.

    Please modify the script as needed.

     

    const myTarget = {
        type: ['grandtotal']
    };
    
    let grand_total = null; //
    
    widget.transformPivot(myTarget, function(metadata, cell) {
        if (metadata.colIndex === 1) {
            grand_total = cell.content; // Store the grand total value of column index 1
        }
        
        if (metadata.colIndex === 2 && grand_total !== null) {
            cell.content = grand_total; // Assign the stored value to column index 2
        }
    });
    

     

    DRay 

    Best regards

  • AssafHanina's avatar
    AssafHanina
    02-19-2025

    hey Martha ,

    thanks for sharing the screenshot.
    it's seems that the per set per the color.

    If I understand correctly, I’ve prepared an example where the script updates the grand total in the columns defined in the columnPairs variable. Below is an example showing the values before and after the update.

    Based on the shared example, please update the columnPairs variable according to the column index (starting from Col0, as shown in the screenshot).

     

    const myTarget = {
        type: ['grandtotal']
    };
    
    // Object to store grand total values
    let grandTotals = {};
    
    // Define column pairs (source → target)
    let columnPairs = {
        1: 5,
        2: 6,
        3: 7
    };
    
    widget.transformPivot(myTarget, function(metadata, cell) {
        // Check if the column is in the columnPairs as a source
        if (columnPairs[metadata.colIndex] !== undefined) {
            grandTotals[metadata.colIndex] = cell.content; // Store grand total value
        }
    
        // Check if the column is a target column (a destination for swapping)
        for (let sourceColumn in columnPairs) {
            let targetColumn = columnPairs[sourceColumn];
    
            if (metadata.colIndex === targetColumn && grandTotals[sourceColumn] !== undefined) {
                cell.content = grandTotals[sourceColumn]; // Assign stored value
            }
        }
    });
    

     

    best regards