Help us improve the AWS re:Post Knowledge Center by sharing your feedback in a brief survey. Your input can influence how we create and update our content to better support your AWS journey.
Quicksight Calculated Field
I am creating vizs in QS and need help with calculations. It is easier for me using PowerBI and DAX which I am more familiar with and can use formulas like ALL, FILTER, Sameperiodlastyear etc to get the results I need, but unfortunately are not available in QS or a way to translate them to equivalent QS formulas.
For example I need to make the sum of a numerical column static rather than the default dynamic aggregation and use as denominator in value calculations by groups Tried the sumvalue function but the Prefilter, preagg and postaggfilter calculation levels are confusing.
I have attached sample data
These are some of the calculations I need to do and repeated for the other dimensions:
Total Amount by year, also by quarter Total Quantity by year, also by quarter
Companyshareamount by year= Amount by Companyandyear/ Total amount for that Year
Company amount PerYear companyshareperyear percentage companytransamountperQt_Yr transamountperQt_Yr companyshareperQt_Yr Year = extract('YYYY', date) Qtr_Yr
?companyshare= sumOver(sum(amount),[company], POST_AGG_FILTER)/sumOver(sum(amount), [])
?companyshareamountperQt for selected year = CompanyamountperQt/totalamountperQt
Company share for selected year- share for previous year
I have created year filters and parameters too Kindly help. Thanks in Advance
- Newest
- Most votes
- Most comments
In Amazon QuickSight, you often need to use calculated fields to perform the same kind of data analysis that you would with DAX in Power BI. While the specific functions like ALL or SamePeriodLastYear don't have direct equivalents, you can often achieve similar results with QuickSight functions like sumOver, avgOver, countOver, etc., combined with the use of PRE_FILTER, PRE_AGG, and POST_AGG_FILTER level-of-detail controls.
Total Amount by Year and by Quarter:
sumOver(sum(amount), [year], PRE_AGG)
You would typically use the
sumOverfunction, which calculates the total over a defined dimension or dimensions, without filtering out the current context.
For quarters, you'll need a calculated field that extracts the quarter from the date, and then use it in a similar sumOver function:
sumOver(sum(amount), [year, quarter], PRE_AGG)
Company Share Amount by Year:
sumOver(sum(amount), [company, year], POST_AGG_FILTER) / sumOver(sum(amount), [year], PRE_AGG)
Company Share Amount per Quarter for Selected Year:
sumOver(sum(amount), [company, quarter], POST_AGG_FILTER) / sumOver(sum(amount), [quarter], PRE_AGG)
Company Share for Selected Year Minus Share for Previous Year:
// Here you will calculate the company share amount for the current year var currentYearShare = sumOver(sum(amount), [company, year], POST_AGG_FILTER) / sumOver(sum(amount), [year], PRE_AGG) // Here you willl calculate the company share amount for the previous year var previousYearShare = sumOver(sum(amount), [company, (year - 1)], POST_AGG_FILTER) / sumOver(sum(amount), [(year - 1)], PRE_AGG) // Here you will subtract the previous year share from the current year share currentYearShare - previousYearShare
For these calculations, you'll need to ensure that
yearandquarterare recognized as dimensions in your dataset, and you'll need to create calculated fields that parse the year and quarter from thedatefield.
Note:
Regarding the Prefilter, preagg, and postaggfilter levels:
- PRE_FILTER: Calculations are made before any filters are applied.
- PRE_AGG: Calculations are made after filters are applied, but before aggregation.
- POST_AGG_FILTER: Calculations are made after aggregation, useful when you want to calculate percentages or differences after summing up the values.
Resources:
Relevant content
- asked 4 years ago
Thank you very much!
sumOver(sum(amount), [year], PRE_AGG) (error msg says no aggregation when using pre_agg or pre_filter)
sumOver(sum(amount), [company, year], POST_AGG_FILTER) / sumOver(sum(amount), [year], PRE_AGG) (error msg says mismatched aggregation, can't have both aggregated and non-aggregated custom calculation together in any form)