Calculating different metric for different seasonality

Q&A
, , ,
1

Hello,
I have 1 year data and I would like to calculate the weekly/monthly/daily average of a column.
Is there a way to do it?

2

Hi @remba87, could you please go through this below Demo Dashboard link.

https://democentral.learnquicksight.online/#Analysis-TipsAndTricks-Calculation-Switch-Date-Aggregation

Thanks & Regards
Biswajit Dash

3

Hello, thank you for your answer, maybe I should argument a little.
I have the data control that switches from one seasonlaity to the other.
however I would like to add in a pivot table the average for that “season”.

image
image393×356 8.5 KB

the cacluated field is
sum({Picking unit})/max({max picking per pallets})
I want to calculate the average of all the data shown there as a new column. OI tried in many ways but still no success.

4

You want to calculate the average of the calculated field “sum({Picking unit})/max({max picking per pallets})” for each “season” or group of data.

Here’s how you can achieve this in Amazon Quick Sight:

  1. Create a calculated field to calculate the metric you want to average:
SUM({Picking unit}) / MAX({max picking per pallets})
  1. Create another calculated field to calculate the average of this metric for each “season” or group:
AVG(SUM({Picking unit}) / MAX({max picking per pallets})) OVER (PARTITION BY <your_seasonality_column>)

Replace <your_seasonality_column> with the name of the column that represents the “season” or group you want to calculate the average for.
3. Add this new calculated field to your pivot table or any other visualization you want to use.

The OVER (PARTITION BY <your_seasonality_column>) part of the formula will calculate the average for each distinct value in the <your_seasonality_column>, effectively giving you the average for each “season” or group.

This approach should allow you to add the average of the calculated metric as a new column in your pivot table, with the average value displayed for each “season” or group.

Let me know if you have any further questions or if you need any clarification on the steps.

5

Hello,

thank you for your help! I did not manage to tansfer the second syntax to a QS calculated field.
However I spend some time thinking and I think that the following calculated field should work
avgOver({pallet used per item},[{data_control},{item number}],PRE_AGG)
where

{pallet used per item}=SUM({Picking unit}) / MAX({max picking per pallets})
HOwever I have an error that says that I can not use Pre_AGG and Pre_Filter on aggregated operands.