# Calculated field for avg distance

Hello all I am trying to create a calculated field that calculates avg Distance depending on the user’s selection i.e Daily avg, weekly avg, monthly avg and yearly avg. I tried this with no success

CASE [Frequency]
WHEN “Daily” THEN window_avg(distance, 1, “days”)
WHEN “Weekly” THEN window_avg(distance, 7, “days”)
WHEN “Monthly” THEN window_avg(distance, 30, “days”)
END

Hello @olusegun83,

thank you for posting your question.

there are window function for average to calculate (avgOver()) as well as a function to truncate date to the period you specify(truncDate()). so if you could use those functions, I think you can calculate the average for the prriod. the below example is for ‘Weekly’

the selection part for the period can be done by using parameter. the below dashboard shows how to use truncDate() with parameter.

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

hope this helps. please let me know how it goes.

i tried this but keep on getting Error with the syntax
avgOver({Distance}, ifelse(

\${Periodstarting} = 'Day’truncDate(‘DD’,{CREATE_DT}),

\${Periodstarting} = 'Month’truncDate(‘MM’,{CREATE_DT}),

\${Periodstarting} = 'Quarter’truncDate(‘Q’,{CREATE_DT}),

truncDate(‘YYYY’,{CREATE_DT})),PRE_FILTER)

would you be able to try with the below ?

ifelse(
\${Periodstarting}=‘Day’, avgOver({Distance}, [truncDate(“DD”,{CREATE_DT})],PRE_FILTER),
\${Periodstarting}=‘Month’, avgOver({Distance}, [truncDate(“MM”,{CREATE_DT})],PRE_FILTER),
\${Periodstarting}=‘Quarter’, avgOver({Distance}, [truncDate(“Q”,{CREATE_DT})],PRE_FILTER),
avgOver({Distance},[truncDate(“YYYY”,{CREATE_DT})],PRE_FILTER)
)

please let me know how it goes.