Use a calculated field as constant reference

Question & Answer
,
1

I have a calculates field avg_december like avgIf({sales},date<parseDate('12/31/2022','MM/dd/yyyy')) and I am using this calculated field as a line reference in a line chart (aggregated by average) but when I apply the date filter this value changes, Is there any way to bypass the filter?

2

Try using this:

avgOver(ifelse(date} < parseDate('12/31/2022', 'MM/dd/yyyy'), {sales}, NULL), [], PRE_FILTER)

You need to use PRE_FILTER in this position to avoid the filters affecting the value.

PRE_FILTER and PRE_AGG are not supported with avgif, but using ifelse here should work how you have it set up in your calculation.

You can read more about how these function here:

2 Likes