Hi!
I have a dataset with the following attributes: Country, City (which aggregates into country), Ship Method Type, Ship Method (which aggregates into ship method type), year, week, date, hour, prediction and actual value.
From this dataset, I want to calculate an accuracy metric which is: abs(prediction - actual) / stdev (deviation of the ship method type) - represented in the screenshot below:
On Quicksight, I want to display Week → Expanding into Country → Expanding into City → Expanding into Ship Method Type → Expanding into hours. The values displayed would be: Sum(Prediction), Sum(Actual) and Metric.
Then, if the user colapses the Ship Method Type, the values displayed are: Sum(Prediction), Sum(Actual) and Average(Metric) of the hours. Which, from the example, for MOSC1 would be the: Sum(I2:I25), Sum(J2:J25) and Average(L2:L25).
I have tried the following:
-
Create a calculated field of partition_actual = sumOver(actual,[{country},{city},{date},{ship_method_type}],PRE_AGG)
-
Create a calculated field of partition_prediction = sumOver(prediction,[{country},{city},{date},{ship_method_type}],PRE_AGG)
-
Create a calculated field of partition stdev = stdevpOver(abs({partition_actual }-{partition_prediction}), [country, {city},{ship_method_type},{gh_in_date}],PRE_AGG)
-
Create a calculated field of metric = abs({partition_actual }-{partition_prediction })/{stdev}
Problems:
- stdev values don’t add up to calculations performed manually on excel.
- On field 4) metric, the numerator is x2 the intended value.
- If I collapse the Ship Method Type, the values of sum(prediction) and sum(actual) return 0.
How can I calculate my metric field and reach my intended goal?
Thank you so much for all the help in advance!