Hi @Bunnydata to that you can try:
Select “Add Calculation” from the dropdown menu.
Enter a name for the calculated field (e.g. “Avg Sales First 3 Months”).
Use the following formula:
IF(OR({Month of Sales}=1,{Month of Sales}=2,{Month of Sales}=3),AVG({Sales Amount}) OVER (PARTITION BY {Product} ORDER BY {Month of Sales} ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), “”)
This will create a new calculated field that shows the average sales of the first 3 months for each product. If there are no sales in the first 3 months, an empty space will be shown
Average of the average sales of the first 3 months of all products with the same attribute 1 and attribute 2:
Use the following formula:
IF(OR({Month of Sales}=1,{Month of Sales}=2,{Month of Sales}=3),AVG(IF(OR({Month of Sales}=1,{Month of Sales}=2,{Month of Sales}=3),AVG({Sales Amount}) OVER (PARTITION BY {Product} ORDER BY {Month of Sales} ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), “”)) OVER (PARTITION BY {Product Attribute 1}, {Product Attribute 2} ORDER BY {Month of Sales} ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), “”)
This will create a new calculated field that shows the average of the average sales of the first 3 months of all products with the same attribute 1 and attribute 2. If there are no sales in the first 3 months, an empty space will be shown.
As for whether to do this as SQL in data prep for better performance, it depends on the size of your dataset and the complexity of your calculations. If your dataset is large and your calculations are complex, it may be more efficient to do this as SQL in data prep. You can use QuickSight’s SQL editor or connect to your data source using Amazon Athena or Amazon Redshift to write the SQL query
Hope this helps