Performing Calculations across Pivot Cells e.g. Cumulative Sums

Comments

4 comments

  • Avatar
    Megan Moss

    How would I modify this to make the loop break when the value doesn't change from the previous cell? Basically, looking for the highlighted cells to be NULL 

     

    0
    Comment actions Permalink
  • Avatar
    Elliott Herz

    Hi Megan,

    I think this is what you are trying to achieve:

    If so, here is the pivot customization I added:

    widget.on('ready', (el, args) => {
    $('tr', element).each((_, trTag) => {
    var prev = null;
    $('td.p-value', trTag).not('.p-total-col-val, .p-total-row-val').each((_,tdTag) => {
    if(prev === null) {
    prev = $(tdTag).text();
    } else if ($(tdTag).text() === prev) {
    $(tdTag).text('');
    } else {
    prev = $(tdTag).text();
    }
    });
    });
    });

    A bit of a different idea from the original post, but gets the desired result of looping through every row and checking sibling column values.

    Hope this helps!

    Best, Elliott

    0
    Comment actions Permalink
  • Avatar
    Megan Moss

    Hi Elliott!

    Thank you for your help, while it did take care of the issue with repeating, it no longer does the rolling sum by column. For reference, the top table in the screenshot below is an expanded pivot of the original data (bottom table) showing the percent of opportunities closed by year in relation to the year of their initial demo.

    The original screenshot (bottom graph in new the screenshot),  takes that data I sent you and compiles it saying based on the year of the demo, what percent of those demos had closed by the end of the first year, what percent had closed by the end of the second year (so year 1 + year 2) and so on. 

    Is there a way to combine the two scripts? So basically I need it to still do the rolling sum across the columns, but if there was nothing that closed in that year (ex. demos taking place in 2019 could only be counted in year 1 because year 2+ hasn't happened yet, so it should be NULL. It can't be 0, because that would throw off the calculations for the averages). Please let me know if this doesn't make sense. I really tried to explain it as simply as I could, haha. 

     

     

     

    0
    Comment actions Permalink
  • Avatar
    Elliott Herz (Edited )

    Hey Megan,

    You are correct, my script doesn't do the running sums. You should use both of the 2 scripts together to get the desired result. They are both run from the ready event, so you can add my code that's within the ready event to Jay's code that already is declaring the ready event. Please let me know if that makes sense, and hopefully that should solve it.

    Try this:

    widget.on('ready', function(w, args) { 
    //Jay's Code
    var cArr = prism.tableGetColHeaders(element);
    var numC = cArr.length;
    var numR = prism.tableGetRowCount(element);
    for(var r = 0; r < numR; r++) {
    for(var c = 1; c < numC; c++) {
    //get a handle on the table cell value
    var cv = parseFloat(prism.tableGetCellVal(r, c, element));

    //loop through all the cells before
    for(var cc = 1; cc < c; cc++) {
    cv = cv + 0 + parseFloat(prism.tableGetCellVal(r, cc, element));
    }

    $dv = $(prism.tableGetCellDiv(r, c, element));

    //you will need to handle the formatting
    $dv.html(cv);
    }
    }


    //Elliott's Code
    $('tr', element).each((_, trTag) => {
    var prev = null;
    $('td.p-value', trTag).not('.p-total-col-val, .p-total-row-val').each((_,tdTag) => {
    if(prev === null) {
    prev = $(tdTag).text();
    } else if ($(tdTag).text() === prev) {
    $(tdTag).text('');
    } else {
    prev = $(tdTag).text();
    }
    });
    });
    });

    Not sure if this solves the grand totals for both the columns and/or rows. So that might be the one thing that is still off.

    Best, Elliott

    0
    Comment actions Permalink

Please sign in to leave a comment.