Aggregated Tables

Hello everyone, I would like your help in creating an aggregated table. I have seen different ways to create aggregated tables using formulas, but I want to do something different because I have two tables involved in this process. To be specific, I would like to consider both Forecast and Revenue, which are in two different tables with different granularities.



To be honest, if I have Revenue at the same level of granularity as my Forecast, I am able to create my graph. However, I cannot build an aggregated table in my database, and I need to aggregate it in QuickSight. My question is: Is it possible to aggregate my Revenue table to have the same level as my Forecast table, and then unify everything in my dataset? I am trying to avoid creating calculations in my analyses, and at the same time, I am not able to create this aggregated table in my database.

what is the best dataset in my opinion to avoid calculation fields? You can see bellow what I have in mind.

Thank you

Hi @woliveiras - Is your data source is relational? If yes, I believe you can use a custom sql and join feature and create a custom data set as per your requirement.

Regards - Sanjeeb

1 Like

thank you @Sanjeeb2022 . Unfortunately, it is a CSV file generated by the system. and I do not have an ETL tool to help me in this point.

1 Like

Hi @woliveiras - Ok, in that case I do not think QuickSight have a provision to create an aggregate table out of CSV source. This is a good point and it is something we should discuss with QuickSight team to add as a feature request, for sources like csv, excel if QuickSight is able to run some custom sql ( by loading in SPICE and represent as a table), a lot of calculation and aggregation can be done.

Hi @Max @David_Wong - Any feedback on this? Please help.

Regards - Sanjeeb


This is a really good point. Possibly what would be suggested here would be to use Athena, but I wouldn’t want to consider another solution for my architecture right now.

@Sanjeeb2022 If QuickSight allows me to use the same visual, two different datasets, it would also solve this problem, right? I could bring Max and Min from one dataset and show them as a line in a graph, and Sales (that comes from another dataset) I could show as a bar, but I think it is not possible, am I right?

Have you considered using Power Query in Excel to aggregate your CSV data? Your aggregated data will then be an Excel file that you can use as data source in QuickSight.

Hi @David_Wong , thank you for your help again… Sure and i did it. It works perfectly. However, I’m trying to accomplish this using QuickSight. Although I started to consider using Athena, I’d like to avoid additional costs.
This is an important feature, and tecnically, it’s just a temporary table.

How about creating one dataset to aggregate the data in QS and then use that as a parent dataset to create another dataset where you join the aggregated data with your forecast? You can use sumOver to do the aggregation at the dataset level.

Hi @David_Wong, Good call. Let me try. My first dataset would be my Revenue, and I need to add a SumOver based on the dimension that I want to aggregate, right?

I am just wondering, when I add this dataset to another one, how many rows would I be there?!?!

It should be:
sumOver(sum({Sales}),[{Segment},{Sub-Category},{Year}], PRE_AGG)

When you use this dataset as parent in another dataset, you’ll start with the same number of rows.

1 Like

Hi, @David_Wong Thank you, but it did not work. The calculation is correct, but once I have the same number of rows, when I join another dataset (fewer rows), I need to apply max in this calculation (from the new dataset). In that case, it will be better to load the dataset as it is.
Another important thing is that I need to add all dimensions in the same view to have a result as I expected in our “Aggregated Table.”