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.
Forecast:
Revenue:
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
2 Likes
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.
1 Like
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.
1 Like
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.
1 Like
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?
sumOver(sum({Sales}),[{Segment},{Sub-Category},{Year}])
I am just wondering, when I add this dataset to another one, how many rows would I be there?!?!
1 Like
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.
2 Likes
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.”
Hi @woliveiras ,
Checking in. Is the above topic still open and are you looking for a solution ?
Kind regards,
Koushik
1 Like
Hi @woliveiras ,
Mash up of data at different grain can be done by leveraging LAC-A calculations.
We have a similar example (sales vs yearly target) on DemoCentral - Check the Actual vs Target tab on https://democentral.learnquicksight.online/#Dashboard-FeatureDemo-Calculation-Level-Aware-Calculations . You can launch the analysis view from left panel to interact with it further.
I’m marking this as solution to this question now. Please let us know if you have further questions.
Regards,
Arun Santhosh
Hi @ArunSanthosh , let me take a look if we can create another Aggregate table using LAC-A as you suggested. I need to say that I have tried to do that many times, and I was able to do that just in my pre-process. I think LAC-A calculation creates a visual aggregation, but let me try again.
Hi @Koushik_Muthanna thank you for your message. @ArunSanthosh suggested to use LAC-A to create my Aggregate table and I am going to see if it works.
1 Like
