In AWS QuickSight, you might have reports that serve their purpose but require just one or two additional columns to streamline your processes. Author users can edit analyses, include necessary columns, and then publish the updated results directly to the dashboard for their team's access and use.
Analyses Screen:
Upon logging in to Quicksight, navigate to the dashboard where you'll find a menu titled "Analyses" on the left-hand side. Click on this menu to access the analyses screen. From the list of analyses displayed, select the specific analysis that you wish to edit.
Note: You cannot edit dashboards directly. Instead, you need to edit the analysis that corresponds to the dashboard.
Note: If you can view the dashboard report but not the corresponding analysis, please reach out to our helpdesk for assistance. We will locate the relevant analysis and grant access to you if you are an author user. Access to analyses is limited to author users only.
Editing Analysis:
Click on the “Datasets” and “Visualize” buttons in the top left corner to bring up the Data and Visuals panes, respectively
The Data pane displays all available fields that can be added to the table, while the Visuals pane shows the fields currently included in the table. If the Visuals pane appears blank or doesn't reflect the table you intend to edit, ensure that you click on the table itself. This action aligns the panes with the correct dataset for editing purposes.
Adding Columns:
You can add a column to your view in three different ways:
Click directly on the field you wish to add in the Data pane.
Click the three dots next to the field in the Data pane and select "Add to Visual".
Drag and drop the field from the Data pane into the field wells in the Visuals pane.
Note: When you drag and drop fields in your report, you can position them as needed. However, be cautious not to unintentionally remove any columns. If you drag a field on top of another field that's already in the report, it will replace the existing field.
Group By and Value:
In the Visuals pane, you'll find two main sections: Group By columns and Value columns.
Group by Columns: You can add any fields to this section, and it will display the data exactly as it appears in the dataset. If multiple rows in the dataset have identical values for all Group by columns, these rows will merge, removing any duplicates.
Value columns: This section accepts only aggregated data. When you add fields here, they are aggregated (e.g., Sum, Min, Max, etc.) based on the Group By columns you've selected.
Understanding these sections helps in organizing and presenting your data effectively in your visualisations.
In the example shown above, if you have multiple records for Anne Example and you add "Name" in the Group By columns and "Margin (sum)" in the Value section, it will consolidate all margin entries associated with Anne Example into a single entry.
This table displays payslip data for a single worker. By placing the "Name" field in the Group By columns and the "Margin" field in the Value columns, the payslip records for the worker are merged into one row. As a result, the "Margin" field aggregates all payslip entries for Anne Example together using a sum aggregation, showing the total sum of margins across all payslips for that worker.
If you include the "Pay Date" field as an additional Group By field, each payslip is displayed on a separate row. With one row per payslip, the "Margin" field in the Value columns aggregates only the margin for that specific payslip, showing the Margin amount corresponding to each payslip row. This setup ensures that the visual representation reflects the distinct margins associated with each payslip, providing a detailed breakdown rather than a consolidated sum across all payslips.
Reordering Columns:
You can reorder a column in two ways:
Dragging and dropping in the Visuals pane: Simply click on the column header and drag it to the desired position within the table.
Using the "Move" option under column headers: Click on the column header in the table, then click the arrow buttons under the "Move" option to move the column left or right to adjust its position.
Note: The second option is the sole method for reordering Group By columns to be positioned to the right of Value columns, and vice versa.
Renaming Columns:
To rename columns, click on the table and then click the "Format visual" button located in the top right corner of the visual. This action opens the Properties pane on the right side of the screen, where you can make adjustments, including renaming columns.
Expand the “Group-by column names” and “Value column names” options to rename the columns as desired.
Editing Columns:
You can edit a column in two ways:
By clicking the three dots next to the field in the Visuals pane.
By clicking the column headers directly in the table.
Formatting:
Clicking on "Format" enables you to adjust the display format of data in the table based on its data type:
All fields: You can modify how NULL values are presented.
Numbers: Options include changing separators, decimal places, units, and how negative values are displayed (either as a minus sign or within brackets). Additionally, the "Show as" feature offers various common formatting options for numbers.
Dates can be edited to change the date format, including custom date formats.
Ordering:
Clicking on "Sort by" enables you to arrange data by a specific field in either ascending or descending order. You can also sort by fields that aren’t visible columns in the table or create custom sorting by using multiple columns as criteria.
Hiding:
Clicking on “Hide/Show” allows you to hide or show columns in the table.
Freezing:
Clicking on "Freeze column" enables you to lock a selected column in place, ensuring it remains visible even when you scroll horizontally through the report. The frozen column will automatically be positioned to the left of all unfrozen columns. This feature is useful for keeping critical information readily accessible as you navigate across the table horizontally.
Note: Frozen columns can never be positioned to the right of unfrozen columns.
Clicking on "Freeze up to this column" lets you lock all columns to the left of and including the chosen column. Unlike freezing a single column, this action does not reposition any columns initially. However, if you later unfreeze columns to the left of the selected one, it will then shift to the right of the remaining frozen columns. This feature helps maintain a fixed section of columns for reference as you navigate horizontally through the report.
You will also get the option to “Unfreeze all columns” if one of your columns is frozen.
Aggregating (only for value columns):
Clicking on "Aggregate" within the Value columns section enables you to modify how the column aggregates data—determining how it handles grouped values. Each data type has specific aggregation options available:
Text data (String): Can be aggregated by Count or distinct Count (the same as Count, but doesn’t double-count duplicate values).
Date data: Can be aggregated by Min or Max dates (which display the earliest and latest date respectively).
Numeric data: Can be aggregated using functions like Sum, Average, median, and other statistical aggregations.
Removing:
Clicking on "Remove" allows you to delete a column from the table. Alternatively, you can drag the column out of the field wells to remove it from the visualisation.
I hope this article has provided you with the information you need. If you require further assistance, please don't hesitate to contact us 😊