Grouping with TOTALS row?

James T Sidebotham
James T Sidebotham Contributor
edited December 2023 in General

I have a grouping of data that I have an aggregation of (sum) for various data points (about 60 rows). Could I add a final row that provides a total for each of the aggregation columns (as seen in Excel).

Thanks

Answers

  • James Sidebotham
    James Sidebotham Member

    FYI, my current workaround is to send the data from the grouping and perform another sum grouping on all of the columns (not the first column of data point. I then perform a union of those two grouped datasets and this gives me what i want. But was hoping for something a bit easier / built-in. thanks

  • Josh Boxer
    Josh Boxer Administrator
    edited September 2023

    Hi James

    You are correct, a Group then Union is how I would achieve this:

    1. Group
    2. Delete default 'Count' aggregate and add a new aggregate
    3. Select 'Multiple' and set Name to blank
    4. Select all (numeric) columns
    5. set Aggregate to Sum and apply

    —> Then Union this with the existing data

    Optional, but depending on what you are doing with these totals, you could format / style the totals rows using a Transform before the Union. Example comma separating these likely large numbers and coloring the totals row to make it stand out:

    image.png 
    SetCellStyle(
    
_RegexReplace(value, '(?:^|\G)(\d{1,3})(?=(\d{3})+(?:.\d+)?$)', '$1,', false),
    
'Success'
    
)

    https://docs.experianaperture.io/data-quality/aperture-data-studio-v2/get-started/create-functions/#function-script-editor

  • James Sidebotham
    James Sidebotham Member

    I wish the cell style would persist when exporting to Excel - would be nice to keep the colours.

Categories