[SOLVED] I want to use a calculated field as a constant reference value, but it keep changing with the filter

I want to use a calculated field as a constant reference value, but it keep changing with the filter

I have a column Sales and a column date, I want to use the average sales from month 10 (October) as reference to compare with the others months.

I made a calculated field Sales_december like avgIf(Sales,date<parseDate('10/31/2022','MM/dd/yyyy'))

*10/01/2022 is the first date in the date column
I'm using Sales_december as a reference line in a line chart, every time I filter the date to a specific month Sales_december goes to zero. The only solution I found was to create a field Sales_december_const with the average value of Sales_december, Ex.: Sales_december_const =2000

Solution

avgOver(ifelse({date} < parseDate('10/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.