Way for PivotGrid value column to represent difference from previous column(s)

Posted by: nwallace on 7 October 2022, 2:24 am EST

    • Post Options:
    • Link

    Posted 7 October 2022, 2:24 am EST - Updated 7 October 2022, 2:29 am EST

    I am trying to create a summary pivot grid that has the following columns:

    • total amount of items in scope
    • total amount of items completed
    • total items remaining to be completed

    Is there a way to make the 3rd column represent the difference between the previous 2 columns? Or is there some alternate way to accomplish this? I tried making the third column a second instance of “total amount of items in scope” and using a showAs calculate of the difference from the previous column, thinking that this would give me the difference I am looking for. However, this produced blank cells, so I must be misunderstanding how the showAs works.

  • Posted 9 October 2022, 10:45 pm EST

    Hello,

    You can modify the data before passing it into the pivotEngine as per your requirement i.e. calculate the difference prop value and update it into the data source. Please refer to the sample link below demonstrating the same:

    https://stackblitz.com/edit/js-zamkjj?file=index.js

    Let us know in case your requirement is different from the above response.

    Regards

  • Posted 11 October 2022, 2:04 am EST - Updated 11 October 2022, 2:11 am EST

    I do see that as an approach. Thank you for explaining that.

    When I have a pivot grid set up like so:

    if I set the showAs on the column “Current Status” to “% difference of previous column,” I expect the cells to display the percent difference between the current column value and the previous column value; so for row 1, that would be 171/2865 = 6%; instead, all the cells are blanked out. Is this not how showAs works? Why is the data blank? If it helps, I am filtering the data in both columns using the “filter” option in the field settings.

  • Posted 12 October 2022, 5:05 pm EST - Updated 12 October 2022, 5:17 pm EST

    Hi,

    You’ll need to use columnFields to use the ‘Difference from previous column’ in ‘showAs’ field setting, in your case the data fields are blank because there is no columnField in the grid, please refer to below screenshots for better understanding -

    Added ‘Buyer’ field in columnFields in above image, so you can see 4 columnField columns (Dad,Kelly,Mom,Sheldon) + Grand Total column. Now when you use ‘show as difference from previous column’, you’ll observe that the difference in current valueColumn is calculated from the same valueColumn of previous columnField, see screenshot below -

    Here, ‘Difference’ and ‘Difference2’ both valueColumns have same data, ‘showAs’ is set to ‘No calculations’ in ‘Difference’ column, and ‘Difference from previous column’ in ‘Difference2’ column. This is basically how the ‘Difference from previous column’ setting works. However, you can observe that there is no data in first columnField’s ‘Difference2’ column,

    This is because this is the first columnField, there is no previous column to calculate data from.

    You’ll observe similar outcome, when you use ‘% Difference from previous column’, see below screenshot -

    You can refer to this updated sample for it’s implementation https://stackblitz.com/edit/js-f62juz?file=index.js

    Regards

Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels