avgOver error

[Manoj_Virigineni]

Hello community,

Following is my Input Data

Below is my desired First Output

• Here sum_total_products is calculated as sum of total products for each date from Input data. i.e on 2024/07/29 → 12+15+20 = 47.
• sum_day_to_day_trans was calculated using same logic.

Below is my desired output2

• Assuming 2024/07/29 as start of the week, then avg_total_products is calculated as (47 + 60+64+65) / (number of dates in a week usually 7 but it is 4 in this case) = 59
• sum_day_to_day_trans is just addition of sum_day_to_day_tran from output1
• trans_per_1000 is (sum_day_to_day_trans/avg_total_prodcuts)*1000. In this case (80/59)*1000 = 135.59

I have tried avgOver and sumOver combinations in quicksight but unable to achieve what I needed. Thanks in advance.

[duncan]

Hello @Manoj_Virigineni !

When you say that you were not able to achieve what you needed, do you mean that your calculated field would return an error or that the values returned were inaccurate?

Could you share the avgOver calculated field that you created that wasn’t working as expected?

[Manoj_Virigineni]

Hello @duncan,

sumOver(sum({total_products}), [Date]) named as sum_total_products to get Output1

avgOver(sum({sum_total_products}), [Date]) which is throwing me an error. It just says error found not sure what exactly the error is.

Irrespective of what I have I tried, but I would like to achieve Output2 table as my end result.

Thanks in advance.

Best,
Manoj

[duncan]

Hello @Manoj_Virigineni !

For the first two field you should be able to achieve this without using a calculated field and instead just using the field well aggregations. Also, if you remove the Accounts field from your visual the table will group by date.

(1)

(2)

For your third calculated field could you try the following calculated field:
(sum(Sales, [{Order Date}])/avg(Quantity, [{Order Date}]))*1000

Let me know if this works!