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 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

Martha

Posted 1 year ago · Edited 1 year ago·Last reply 1 year ago

9 comments

DRay

·1 year ago

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.

DRay

·1 year ago

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.

Assaf Hanina

·1 year ago · Edited

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

OP1 year ago

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
        }
    })
});

Assaf Hanina

·1 year ago

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

OP1 year ago

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).

Assaf Hanina

·1 year ago · Edited

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

OP1 year ago

Thank you again!

This is exactly what I needed.

Assaf Hanina

·1 year ago

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