PRE_FILTER not working on sumOver(distinct_countif())

Im having trouble using the PRE_FILTER parameter. the calculations are below.

calc1
sumOver(distinct_countIf({customer_id}, {date} >= {curr_month_start_date}), [], PRE_FILTER)

calcl2
sumOver(sumif({total_amount}, {date} >= {curr_month_start_date}), [], PRE_FILTER)

Context: I have other columns that might be used for filtering like region. What I want is even if the region is filtered, I still want the calculation to be computed based on the total sum of the whole dataset for that column.

However, Im having this error:
For calculation levels PRE_FILTER and PRE_AGG, the operands can’t be aggregated.

Hi chanQS,

It the throwing the “For calculation levels PRE_FILTER and PRE_AGG, the operands can’t be aggregated.” error because within the sumover() function, you are using the aggregation function such as sum and count

Can you try following for cal1:
distinctCountOver(ifelse({date} >= {curr_month_start_date}, {customer_id}, NULL), , PRE_FILTER)

and following for cal2:
sumOver(ifelse({{date} >= {curr_month_start_date}, {total_amount}, 0), , PRE_FILTER)

1 Like

I tried that but the error now is:
PRE_AGG_CALCULATION_LEVEL_MISMATCH

I noticed that when I add it to the visual, its using (Min) instead of custom.

image

Hi @chanQS,

There are 2 important things to note on this. First of all, the LAC-W aggregation will by default display as a min aggregation in the field well because the nature of the function will return the same value on multiple rows. You only want to return a single instance of the value to ensure you are getting the correct response. Max, Min, or Median would all return the same thing, and you would want to avoid Sum or Avg.

As for the PRE_AGG_CALCULATION_LEVEL_MISMATCH error, is one of the fields you are using within the sumOver or distinctCountOver calculations another LAC-W aggregated field? It looks like you are trying to nest a function on the PRE_AGG level within a function on the PRE_FILTER level. This will not function correctly, and both calculations need to match for this to work.

I will mark my response as the solution, but please let me know if you need any further guidance. Thank you!