Replacing Total with different column in Pivot2

[Martha]

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 even display that in the same pivot2 table.

Hiding one of the columns should be possible according to this post: https://community.sisense.com/t5/build-analytics/pivot-2-0-hiding-columns-from-the-display/m-p/3607

So now I just need a way to overwrite the Total calculation of my column "correct values" with the Total value from the column "correct Total".

any suggestions?

Thx, Martha

[AssafHanina · Accepted solution]

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 · Accepted solution]

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

[Martha]

Thank you, this looks really good!

I have not just one, but several pairs of columns - and I'm struggling to get get the for-loop correct. In my version, both Totals get replaced with the Total of the very last column 😕

could you help again please?

const myTarget = {
    type: ['grandtotal']
};

let grand_total = null; //
columns = [1,2]
widget.transformPivot(myTarget, function(metadata, cell) {
    columns.forEach(function(col) {
        if (metadata.colIndex === col+5) { // the columns to be switched are 5 apart from each other
            grand_total = cell.content; // Store the grand total value of column index +5
        }
    
        if (metadata.colIndex === col && grand_total !== null) {
            cell.content = grand_total; // Assign the stored value to column index from list
        }
    })
});

[AssafHanina]

Hey @Martha ,

Thanks for sharing the feedback!

can you please share a screenshot of the Pivot without exposing the data (it can run on sample Retail datasource) and mark on the screenshots which pair of column/s should be updated?

best regards

[Martha]

Hi @AssafHanina ,

here is a screenshot, having several columns, where I need to replace the Total with the Total from another column. In between I have a couple of other columns. that's why in my example code I use the col+5 to skip over all the other columns (would need to be col+7 from this screenshot, but you get the gist).

 

[AssafHanina · Accepted solution]

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

[Martha]

Thank you again!

This is exactly what I needed.

[AssafHanina]

you are welcome! happy to hear it's resolving the issue

[DRay]

Hello @Martha,

I’m following up to see if the solution offered by @AssafHanina worked for you.

If so, please click the 'Accept as Solution' button on his post. That way other users with the same questions can find the answer. If not, please let us know so that we can continue to help.

Thank you.

[DRay]

Hello @Martha,

I’m following up to see if the solution offered by @AssafHaninaworked for you.

If so, please click the 'Accept as Solution' button on their post. That way other users with the same questions can find the answer. If not, please let us know so that we can continue to help.

Thank you.