My goal is to calculate the standard deviation of revenue divided by days of the month, with final visual aggregation by region (region) in the rows and by product (products) in the columns.
To determine the number of working days (qt_days) in total: distinct_count({dates}, [{products}])
(We did this because not all products sell every day, but I need to consider every day when creating the daily average for the month).
We separated the sales of each product so that we could compare them by creating a column for each one (sales_product1, sales_product2âŚ).
To calculate the average monthly billing value (avg_product1): sum({sales_product1})/sum({qt_days})
Standard deviation (stdev_product1): stdevOver({avg_product1},[region])
The result is incorrect, how should I apply this formula?
I was able to achieve right results for qt_days and avg_product1. However, stdev_product1 is wrong.
Variables that Iâm using:
region - region (string)
products - products (string)
dates - days (date)
sales_product1 - sum of the revenue for the product 1.
Hi @Lucas_Dellarole and welcome to the QuickSight community!
What about your result is incorrect? Are you receiving an error code or are the values just not matching how you expect (and if this is the case, by how much)?
The formula is working, but the result is incorrect.
We are averaging revenue per day, so adding up what we sell per day and dividing by the number of days.
We want the deviation from this average, but we cannot achieve the value.
We obtained the standard deviation of the average overall sales, considering sale by sale, but not the average day by day.
Attached I placed some images of product1âs data in a region.
We do not sell this product every day, but we need to consider the total number of days in the period of my panel, for example here I am considering from 08/01/2024 to 08/30/2024
In the âImagem1â, we have all the sales of product1 in this time period and the result that we achieved (I had to separate the table, because of the upload limits here).
In the âImagem2â are the dates we need to consider, in the second column is the sum of sales by the day and, in the third column, is the result we are trying to achieve.
I switched over the calc. fields to LAC-W calculations and added âPRE_AGGâ, Iâd be interested to see how that affects your numbers and produces something more accurate!
