Calculated field for avg distance

Question & Answer
, ,
1

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

2

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’

avgOver(distance,[truncDate(“WK”,[your date field)],PRE_FILTER)

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.

3

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)

4

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.